Syntex Help

[jnevada]

Could anyone let me know if the syntex of where clause is correct?



........
"where FName like '" %Sher% "' " & _
..............


Thanks a lot.

[CreganTur]

From the looks of things you're trying to use a VBA variable value in a SQL string. This is a very common thing.

Please provide the whole code so I can see exactly what's going on, and please use the VBA tags (the green VBA button) because it will format your code according to VBIDE :thumb

[jnevada]

Thanks Randy for your quick response. You are right, I'm trying to query all the fname comtaining "sher". Here is the code


strSQL = "Select lname, fname from table1 " & _
"where fname like '" %Sher% "' " & _
"and lname = smith"

[CreganTur]

Try this:
strSQL = "Select lname, fname from table1 " & _
"where fname like '" %Sher% "' and lname = 'smith'"

smith is still a string value, even though you are hardcoding it into your SQL statement. Therefore, it needs to be wrapped in single quotes so that SQL knows it is looking at a string.

See if that solves your problem.

[jnevada]

I got: compile error, invalid char %.

[CreganTur]

I got: compile error, invalid char %.

I figured that would happen. Change your %Sher% variable to just Sher. You do not need any special symbols to declare variables, just make sure you declare it in your procedure. And also make sure you're using Option Explicit if you're not already- it forces you to declare all your variables. Not only is this best-practice, but it's an invaluable help when you have to debug your code.