Access VBA: Cannot combine variable and string in SQL query

Question

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?

Please show more of your code as the concatenation of your strings should work according to the currently shown code. However, building a SQL statement like so is the classic way of SQL injection ... — DAXaholic
– DAXaholic, Commented Jul 14, 2016 at 8:08

HansUp · Accepted Answer · 2016-07-21 11:49:52Z

1

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

edited Jul 21, 2016 at 11:49

answered Jul 15, 2016 at 13:47

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Doug Over a year ago

Thanks! I still have some question regarding your answer. The strSelect = "SELECT TOP 1 i.Code FROM [Item] AS i WHERE i.User = [which_user] ORDER BY i.ID would give me a SQL string with the fixed/non-variable [which_user] in the SQL, right?. And the qdf.Parameters("which_user").Value = strUserName would change this fixed [which_user] in the SQL string above? What do I put in the double-quote instead of "which_user"? The [which_user] variable from the SQL string? Thank!

HansUp Over a year ago

which_user is a parameter. A query parameter is nothing more than a placeholder for a value which you must supply when the query is run. It should make more sense if you paste the SELECT statement into SQL View of a new Access query and then run that query: SELECT TOP 1 i.Code FROM [Item] AS i WHERE i.User = [which_user] ORDER BY i.ID DESC

Doug Over a year ago

Thanks HansUp. My problem is that I cannot manage to insert/change the variable "which_user" parameter into/in the strSelect. So my SQL string literally remains strSelect = "SELECT TOP 1 i.Code FROM [Item] AS i WHERE i.User = [which_user] ORDER BY i.ID. The [which_user] should be A_user right? So that the strSelect would be strSelect = "SELECT TOP 1 i.Code FROM [Item] AS i WHERE i.User = 'A_user' ORDER BY i.ID before the "qdf.OpenRecordSet", correct? Sorry, I am really a newbie:)

HansUp Over a year ago

No, that's not correct. But I'm unsure how to approach this now. Have you tested the query in the Access query designer as I suggested yesterday?

Doug Over a year ago

Yes, I did. As I run the query, an input box pops up and I have to insert an user name for the parameter which_user. After insert the right user name into that box and click OK, I get the correct result. Does it has something to do with my user name, which is the Windows user name fetched by using this function:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _     "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

For now, I still stuck at the Set rs = qdf.OpenRecordset. The code run into an error there (Error 13, Type Mismatch)

|

ThunderFrame · Accepted Answer · 2016-07-14 08:28:55Z

0

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;"

answered Jul 14, 2016 at 8:28

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

3 Comments

ThunderFrame Over a year ago

But as per DAXaholic's comment above, beware of SQL injection using this approach

Doug Over a year ago

I've tried these solutions but it did not help :( fontstuff.com/access/acctut15.htm The & """" & " ORDER BY ID DESC;" in my strSQL is still cut off!

ThunderFrame Over a year ago

Perhaps you have a typo in the variable name strSQL? Also notice that my example assigns the variable on the first line, and then appends to the variable on the second (i.e. the variable appears on both sides of the = sign on the second line).

Collectives™ on Stack Overflow

Access VBA: Cannot combine variable and string in SQL query

2 Answers 2

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related