Ques on SQL "Select..." statement in Access 2000

[batteam]

I'm trying to pull some records from a table using the Like Operator. My code, behind a form is similar to:

varName = "B"
strSQLNames = "Select * from tblNames where last_name Like '" & varName* & "'" (note: all code is on same line in my project).

But of course I get a syntax error. I've tried multiple combinations but cannot get the correct syntax. Can anybody assist me with the correct syntax that would, in my example, give me all the last names that begin with B, using a Select stmt in that way?

Thanks for any assistance you can offer.

 

[cmmrfrds]

To look at how your sql statement is constructed, after changing the wild card character to %.

varName = "B"
strSQLNames = "Select * from tblNames where last_name Like '" & varName% & "'" (note: all code is on same line in my project).

Debug.Print strSQLNames

and then do Control G to look at the statement.

 

[lameid]

You have two problems. First the wildcard needs to be in as part of a string because it is a literal you are concatenating together. Secondly, since you are using an ADP and therefore SQL server is your backend, you need to use SQL server's syntax for SQL statments. SQL server uses % for any number of characters instead of *. This is what cmmrfrds was getting at.

strSQLNames = "Select * from tblNames where last_name Like '" & _ 
varName & "%'"

 

[genomon]

You don't need to concatenate the wildcards (get rid of & character); if you did, you would need to use the + character.

strSQLNames = "Select * from tblNames where last_name Like '%varName%'"

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[lameid]

In batteam's example he is building an SQL string based on the variable he has in VBA, "varName". Therefore my example is correct. If on the other hand he wanted to find everything containing the text "varName" then genomon's code would be correct.

 

[batteam]

Thanks to all. I am searching for the value of the variable, in my case varName

lameid- I can get your syntax to work, but unfortunately I meant to put the wildcard character BEFORE my variable, not after as I show in my example. Try as I might, I cannot get that syntax correct. Any suggestions on this would be appreciated.

Thanks again for your responses.

 

[lameid]

strSQLNames = "Select * from tblNames where last_name Like '%" & _ 
varName

Note I am using the underscore as the line continuation... That may have been throwing your syntax. You can take out the underscore and put everything on the same line.

 

[cmmrfrds]

You probably need to put the wildcard both before and after since if there are trailing spaces it would not find it, or do a right trim on the last_name.

strSQLNames = "Select * from tblNames where last_name Like '%" & varName & '%'

--OR do
rtrim(last_name)