VBA SQL using a variable in sql query (1 Viewer)

exaccess

Registered User.
Local time
Tomorrow, 00:15
Joined
Apr 21, 2013
Messages
287
What is wrong with this statement.

Code:
"SELECT * FROM [TransTbl] WHERE [TransTbl].[Assoc] = & 'InAssoc' & " & _
       "ORDER BY [TransTbl].[LastName]"

TransTbl is the table on which the query will run.

Assoc and LastName are fields of the table. InAssoc is defined text variable which can change each time the query is executed Please help.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:15
Joined
Sep 21, 2011
Messages
14,048
You need to concatenate the strings with the variables. I've not done this over two lines so would do
Code:
strSQL = "SELECT * FROM [TransTbl] WHERE [TransTbl].[Assoc] = '" & InAssoc & "'" 
strSQL = strSQL & " ORDER BY [TransTbl].[LastName]"

or
Code:
strSQL = "SELECT * FROM [TransTbl] WHERE [TransTbl].[Assoc] = '" & InAssoc & "' ORDER BY [TransTbl].[LastName]"

I believe the first method is better as you can then Debug.Print strSQL and check the syntax.

HTH
 

exaccess

Registered User.
Local time
Tomorrow, 00:15
Joined
Apr 21, 2013
Messages
287
You need to concatenate the strings with the variables. I've not done this over two lines so would do
Code:
strSQL = "SELECT * FROM [TransTbl] WHERE [TransTbl].[Assoc] = '" & InAssoc & "'" 
strSQL = strSQL & " ORDER BY [TransTbl].[LastName]"

or
Code:
strSQL = "SELECT * FROM [TransTbl] WHERE [TransTbl].[Assoc] = '" & InAssoc & "' ORDER BY [TransTbl].[LastName]"

I believe the first method is better as you can then Debug.Print strSQL and check the syntax.

HTH

Gasman it works like charm. Thank you.
 

sxschech

Registered User.
Local time
Today, 16:15
Joined
Mar 2, 2010
Messages
791
Multi-Line version:
Code:
strSQL = "SELECT * " & _
	 "FROM [TransTbl] " & _
	 "WHERE [TransTbl].[Assoc] = '" & InAssoc & "' "  & _
	 "ORDER BY [TransTbl].[LastName]"
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:15
Joined
Jan 20, 2009
Messages
12,849
Another way is to use VBA to pass the variable to a querydef as a parameter.
 

Users who are viewing this thread

Top Bottom