Open form using wildcard 1

[kieranplatt1]

Hi, hope someone can help. I am trying to open a form to display certain records i.e. users type in a surname or part of a surname into a field 'goto' - I want the form to display all records where either 'surname1' OR 'surname2' (these are Mr & Mrs records which could have couples with differing surnames) begin with the letters input into 'goto'. I am trying to use wildcard - this is the code I have tried...

Private Sub Command111_Click()
On Error GoTo MyError
DoCmd.OpenForm "ClientsTab", , , "[Surname1] = '" & Me![goto] & "*'" or [Surname2] = '" & Me![goto] & "*'"
Exit Sub
MyError:
MsgBox (Err.Number & "-" & Err.Description)
End Sub

any offers!? Thanks, Kieran.

 

[jrbarnett]

Instead of using = , if you want to use wildcards, use the "Like" word instead:

DoCmd.OpenForm "ClientsTab", , , "[Surname1] Like '" & Me![goto] & "*'" or [Surname2] Like '" & Me![goto] & "*'"

 

[kieranplatt1]

Works fine for SURNAME1 but does not like the syntax for SURNAME2 - it is showing in red - can you use OR in this statement?

regards

Kieran

 

[jrbarnett]

Sorry,

Didn't notice the extra quotes:

DoCmd.OpenForm "ClientsTab", , , "[Surname1] Like '" & Me![goto] & "*' or [Surname2] Like '" & Me![goto] & "*'"

John

 

[kieranplatt1]

Thank you for a speedy and successful post - have a star.

Kieran