How do you add a "LIKE" option to SQL VB Code? (1 Viewer)

[gold007eye]

I am trying to set the RecordSource of a List in the OnOpen even of the form based on the text box "Last Name" that is filled in by the user on the Main Menu. For some reason I can't get the code to work with the "LIKE" command what am I doing wrong?

I am getting Data Mis-Match 13. How can I get the Like portion of the code to work correctly? If I remove the "Like" portion and change it to "=" then I get know errors. I'm lost at what to do next.

Private Sub Form_Open(Cancel As Integer)
Me![Search Results].RowSource = _
"SELECT [Letter Information].[Provider Name], [Letter Information].[Contact Name], [Letter Information].[Letter Type], [Letter Information].[Letter Date], [Letter Information].Analyst, [Letter Information].[Address Line 1], [Letter Information].[Address Line 2], [Letter Information].City, [Letter Information].State, [Letter Information].[Zip Code]" & _
"From [Letter Information]" & _
"WHERE ((([Letter Information].[Provider Name]) Like " * " & [Forms]![Main Menu]![Last Name])" & _
"ORDER BY [Letter Information].[Provider Name];"
End Sub

 

[KenHigg]

"WHERE ((([Letter Information].[Provider Name]) Like ' * '" & [Forms]![Main Menu]![Last Name]) & _

???

 

[Mile-O]

Change:

"WHERE ((([Letter Information].[Provider Name]) Like " * " & [Forms]![Main Menu]![Last Name])" & _

To:

"WHERE ((([Letter Information].[Provider Name]) Like '*" & [Forms]![Main Menu]![Last Name] & "')" & _

You need to ensure that text, in a query, is seen as a string literal. Effectively you are saying Field Like Smith, when what you want is Field Like 'Smith'.

 

[KenHigg]

Miles in the house

 

[gold007eye]

Thanks guys I will try your suggestion when I get into work tomorrow morning

 

[gold007eye]

Syntax Error:

ok.. Changed the code as suggested, but now I am getting a syntax error (missing operator) when the code is run.

Here is the complete code I'm using now.

Private Sub Form_Open(Cancel As Integer)
Me![Search Results].RowSource = _
"SELECT [Letter Information].[Provider Name], [Letter Information].[Contact Name], [Letter Information].[Letter Type], [Letter Information].[Letter Date], [Letter Information].Analyst, [Letter Information].[Address Line 1], [Letter Information].[Address Line 2], [Letter Information].City, [Letter Information].State, [Letter Information].[Zip Code]" & _
"From [Letter Information]" & _
"WHERE ((([Letter Information].[Provider Name]) Like '*" & [Forms]![Main Menu]![Last Name] & "')" & _
"ORDER BY [Letter Information].[Provider Name];"

End Sub

The exact error is:
Sytnax error (missing operator) in query expression '((([Letter Information].[Provider Name]) Like '*smith')ORDER BY[Letter Information].[Provider Name];'

So it looks like the error is just in that portion of the code?

 

[Rabbie]

ok.. Changed the code as suggested, but now I am getting a syntax error (missing operator) when the code is run.

Here is the complete code I'm using now.

Private Sub Form_Open(Cancel As Integer)
Me![Search Results].RowSource = _
"SELECT [Letter Information].[Provider Name], [Letter Information].[Contact Name], [Letter Information].[Letter Type], [Letter Information].[Letter Date], [Letter Information].Analyst, [Letter Information].[Address Line 1], [Letter Information].[Address Line 2], [Letter Information].City, [Letter Information].State, [Letter Information].[Zip Code]" & _
"From [Letter Information]" & _
"WHERE ((([Letter Information].[Provider Name]) Like '*" & [Forms]![Main Menu]![Last Name] & "')" & _
"ORDER BY [Letter Information].[Provider Name];"

End Sub

The exact error is:
Sytnax error (missing operator) in query expression '((([Letter Information].[Provider Name]) Like '*smith')ORDER BY[Letter Information].[Provider Name];'

So it looks like the error is just in that portion of the code?

You have mismatched ( and ). 3 ( and 2 ) in the code you posted.

Try something like

Private Sub Form_Open(Cancel As Integer)
Me![Search Results].RowSource = _
"SELECT [Letter Information].[Provider Name], [Letter Information].[Contact Name], [Letter Information].[Letter Type], [Letter Information].[Letter Date], [Letter Information].Analyst, [Letter Information].[Address Line 1], [Letter Information].[Address Line 2], [Letter Information].City, [Letter Information].State, [Letter Information].[Zip Code]" & _
"From [Letter Information]" & _
"WHERE (([Letter Information].[Provider Name]) Like '*" & [Forms]![Main Menu]![Last Name] & "')" & _
"ORDER BY [Letter Information].[Provider Name];"

End Sub

 

[gold007eye]

Thanks. I tried that before you had replied and that was the problem Now lets see if I can do this for dates and with multiple search options.

 

[gold007eye]

Double WildCards:

Can someone help me convert this to the proper SQL please?

Private Sub Provider_Name_BeforeUpdate(Cancel As Integer)
DoCmd.OpenForm "Search Form"
Forms![Search Form]![Search Results].RowSource = _
"SELECT [Letter Information].[Provider Name], [Letter Information].[Contact Name], [Letter Information].[Letter Type], [Letter Information].[Letter Date], [Letter Information].Analyst, [Letter Information].[Address Line 1], [Letter Information].[Address Line 2], [Letter Information].City, [Letter Information].State, [Letter Information].[Zip Code]" & _
"From [Letter Information]" & _
"WHERE ((([Letter Information].[Provider Name]) Like " * " & [Forms]![Main Menu]![Provider Name] & " * "));"
End Sub

What I am trying to do is use thie search option so that if someone does a search for "smith" it will show:

John Smith
Jane Smith, MD
The Smithsonian Institute

From what I have read to do that you need to have the wildcard as:

Like "*" & Forms![Main Menu]![Provider Name] & "*"

Instead of:

Like *Forms![Main Menu]![Provider Name]*

 

[gold007eye]

I got this one figured out Took a little playing around with the code.. Here is what I used to get it working:

Forms![Search Form]![Search Results].RowSource = _
"SELECT [Letter Information].[Provider Name], [Letter Information].[Contact Name], [Letter Information].[Letter Type], [Letter Information].[Letter Date], [Letter Information].Analyst, [Letter Information].[Address Line 1], [Letter Information].[Address Line 2], [Letter Information].City, [Letter Information].State, [Letter Information].[Zip Code]" & _
"From [Letter Information]" & _
"WHERE ((([Letter Information].[Provider Name]) Like '*" & [Forms]![Main Menu]![Provider Name] & "*" & "'));"

Now on to the other search methods. Thanks for your help everyone.

 

[KenHigg]

"WHERE ((([Letter Information].[Provider Name]) Like '*" & [Forms]![Main Menu]![Provider Name] & "*" & "'));"

Can be condensed to:

"WHERE ((([Letter Information].[Provider Name]) Like '*" & [Forms]![Main Menu]![Provider Name] & "*'));"