using Like statement in where condition for docmd.openform

[hoda64]

Hello,

I want to open a form based on two conditions: a date and a facility name

The problem is that the data I have is not consistent when it comes to the facility name part. For example, if I wanted to search "apple" the data might have "apple" written as "apple1" or "apple2". Therefore it is not enough to just have name = apple because I will miss apple1 and apple2. To get around this, I have been using the "like" statement. So if it is like *apple* then apple1 and apple2 will be included.

I have been trying to write the docmd.openform condition to include the ability to use the "like" statement, but have been unsuccessful. Can someone tell me if that is possible, and if so, what the syntax is? This "where" condition seems to be pretty tricky when it comes to quotations...

This is my latest attempt, I get a type mismatch error:

DoCmd.OpenForm "InputLER", acNormal, , "[Event Date] = Forms!InputCOMPSIS![Date of Event] And [Facility] Like " * " & Forms!InputCOMPSIS!Name & " * ""

Thanks in advance, I really appreciate it!

 

[pbaldy]

Several problems. Try this:

DoCmd.OpenForm "InputLER", acNormal, , "[Event Date] = #" & Forms!InputCOMPSIS![Date of Event] & "# And [Facility] Like '*" & Forms!InputCOMPSIS!Name & "*'"

 

[hoda64]

Thanks!! That worked, I am going to try to figure out what you did. I'll be back with questions if I cant figure it out. Thanks again!

 

[pbaldy]

No problem. Hints: concatenation and delimiters.

 

[NauticalGent]

Several problems. Try this:

DoCmd.OpenForm "InputLER", acNormal, , "[Event Date] = #" & Forms!InputCOMPSIS![Date of Event] & "# And [Facility] Like '*" & Forms!InputCOMPSIS!Name & "*'"

Just over 10 years later and this thread carried the day, thanks Paul!

 

[pbaldy]

Happy to help NG! I'll PM you our schedule, but flying in 10/7.