SOLVED

Docmd.Openform (like)

Steel Contributor

Hello,

 

I have a form and a click event on a field that opens another form based on the Me.DDNo (double format).

I need to modify it to open frmPmtProposalALL for taking into account decimals.  Little hard to explain but here is an example:

DDNo

7

7.1

 

If I am on a record of DDNo = 7 I also want it to open records with DDNo = 7.1.  

DoCmd.OpenForm "frmPmtProposalALL", , , "DDNo =" & Me.txtDDNo

How would I adjust the above? Not sure if its a "like"?  

thank you very much!

7 Replies
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 It depends, in part, on the datatype of this field in the table. It is, I understand, a double ( number which includes decimals to a specified precision).

 

That means Like as an operator is not appropriate. Like is used with Text, or String, values.

 

You can include any number with 7 as the whole number and ANY decimal amount by using a type conversion function to coerce these values:

 

DoCmd.OpenForm "frmPmtProposalALL", , , "Clng(DDNo) =" & Me.txtDDNo

 

should do it. Please try that and report back if it works as required.

 

Perfect George. thank you. Works like a charm.
Hi George, I have a follow up.
I noticed that if for example I am on
txtDDNo = 7 then it will include 7 and 7.1
However
txtDDNo = 7.1 then the form opens with null. Meaning the recordset is neither 7 or 7.1. Form opens null.

do you have a suggestion? thank you
you may also try:

DoCmd.OpenForm "frmPmtProposalALL", , , "DDNo >= " & Me.txtDDNo & " And DDNo < " & CLng(Me.txtDDNo) + 1
Hi Arnel,

thank you for the response. Do you have another idea? It didnt filter for any records. The form that opened on the Click event opened but there were no records. I re-checked the names and they are accurate. Format for DDNo is number, double.

thank you for the help
Arnel, sorry I had a refresh issue. It works great! thank you to you both. Happy New Year!

@arnel_gp 

 

Actually, it seems like I did need to modify it.  I had a subtract .1 to pick up a record less than...meaning if I am on a record Me.txtDDNo = 7.1, the form that loads on the dbl click event would not load Me.txtDDNo = 7 unless I added -.1  It seems to work in my testing.  thank you once again to both. 

 

DoCmd.OpenForm "frmPmtProposalALL", , , "DDNo >= " & Me.txtDDNo & " -.1 And DDNo < " & CLng(Me.txtDDNo ) + 1

1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 It depends, in part, on the datatype of this field in the table. It is, I understand, a double ( number which includes decimals to a specified precision).

 

That means Like as an operator is not appropriate. Like is used with Text, or String, values.

 

You can include any number with 7 as the whole number and ANY decimal amount by using a type conversion function to coerce these values:

 

DoCmd.OpenForm "frmPmtProposalALL", , , "Clng(DDNo) =" & Me.txtDDNo

 

should do it. Please try that and report back if it works as required.

 

View solution in original post