Using Like* statement in Where Clause

[Gina]

I have a form with 10 unbound text boxes. A user will populate between 1 and 10 of these text boxes with report criteria, and click a button to open a report. The data in the criteria field is not consistant (which is a issue I will eventually fix), so if the user is looking for P2522, it could be stored in the table/field as "P2522" or "P2232, P2252, P2311" or "2522". I need to build a where clause for the report in VBA and I can't seem to get it. So far, I am just trying to get text box #1 to work, and will then adapt the code for the rest. I am stuck at the line in RED

Dim stDocName As String
Dim stFilter As String

stFilter = "[TFA_Protocol_Number]=Like '*" & Me![txt1] & "*" & "' "
stDocName = "rptContractsByStudy"
DoCmd.OpenReport stDocName, acPreview, , stFilter

Any help would be appreciated

 

[ajetrumpet]

Gina,

Instead of this...

[color=red]stFilter = "[TFA_Protocol_Number]=Like '*" & Me![txt1] & "*" & "' "

[/color]Try using this one...

stFilter = "[TFA_Protocol_Number] Like '*' & Me![txt1] & '*'"

It's important to remember that when you're writing functions or SQL lines that are REQUIRED in VBA as "strings", the first " (full quote character) you insert after the start of the string will be read as the end of the string. Thus, Access will always tell you that it expects the end of a statement at that point, because a string is noted "in quotes".

To avoid this, use the ' (single quote) as a substitute for the full quote notation inside string values.

 

[Dennisk]

like is an operator and so ie =

so you use one or other, and not both. So your filter should be

stFilter = "[TFA_Protocol_Number] Like '*" & Me![txt1] & "*" & "' "

 

[Gina]

THANK YOU Ajetrumpet, your code worked perfectly. I always seem to have difficulty with ticks and quotes.

Appreciated,
Gina