CODEWITHSUNDEEP
sqlstringms-accessvba
Doug
Doug

Reputation: 265

Access VBA: Cannot combine variable and string in SQL query

I have a list of users (as string variable) and want to use it as the criteria (one user at a time) for my SQL to pick the first item's code. So I put a SQL query as a string to run it later in the code.

I've tried this:

strSQL = "SELECT TOP 1 Item.Code FROM Item WHERE Item.User = '" 
strSQL = strSQL & strUserName & "' ORDER BY ID DESC"

If the strUserName = "A-user" for example, I only get this SQL string, according to "Quick Watch...":

SELECT TOP 1 Item.Code FROM Item WHERE Item.User = 'A-user

The part with "' ORDER BY ID DESC" isn't included in the SQL string at all!

And for running the SQL query by using "Set rst = CurrentDb.OpenRecordset(strSQL)", I get this error:

Run-time error '3075': Syntax error (missing operator) in query expression 'Item.User = "A-user"

How can I fix that?

Upvotes: 1

Views: 1320

Answers (2)

HansUp
HansUp

Reputation: 97101

I don't see how your code triggers that 3075 error. However I would use a parameter query instead. That way you can avoid problems with quoted values in your SQL statement.

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSelect As String
Dim strUserName As String

strUserName = "A-user"
strSelect = "SELECT TOP 1 i.Code FROM [Item] AS i WHERE i.User = [which_user] ORDER BY i.ID DESC"
Set qdf = CurrentDb.CreateQueryDef(vbNullString, strSelect)
qdf.Parameters("which_user").Value = strUserName
Set rs = qdf.OpenRecordset

Upvotes: 1

ThunderFrame
ThunderFrame

Reputation: 9461

Access sometimes complains about the use of single-quotes. And also add a semi-colon to the end of the statement. Try changing the code to this (where double-quotes are escaped with an extra double-quote:

strSQL = "SELECT TOP 1 Item.Code FROM Item WHERE Item.User = " 
strSQL = strSQL & """" & strUserName & """" & " ORDER BY ID DESC;"

Upvotes: 0

Related Questions