VBA SQL using a variable in sql query

[exaccess]

What is wrong with this statement.

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

You need to concatenate the strings with the variables. I've not done this over two lines so would do

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

or

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]

You need to concatenate the strings with the variables. I've not done this over two lines so would do

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

or

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]

Multi-Line version:

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

 

[Galaxiom]

Another way is to use VBA to pass the variable to a querydef as a parameter.