Forum Discussion
Docmd.Openform (like)
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!
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.
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!
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
