Using Variables in SQL

[cpc]

Good day,
I'm stupmed on this one, hope someone can help. I am attemptin to use Form entrees as Variables for my SQL FROM and WHERE clause. For some reason the SQL does not accept variables. Looks too simple but I guess it isn't.
Form has:TrainingType, TableName, Date, EmpName

My sql looks like:

SELECT [Forms]!

![txtTable].[EmpID], [Forms]!

![txtTable].[TrngID], [Forms]!

![txtTable].[Data]
FROM [Forms]!{Table]![txtTable]
WHERE (((tblQA.EmpID)=Forms!Table!txtName) And ((tblQA.TrngID)=Forms!Table!txtTrngType) And ((tblQA.Data)=Forms!Table!txtDate));

Thanks

 

[BigHappyDaddy]

Try building your query in the query designer, then switch to SQL view. Then compare that text with the text you provided here.

You should see the differences.

 

[Steve R.]

You can't "directly" reference a variable. The basic structure of a select statement is:

"SELECT SomeField FROM tblTable WHERE SomeField=SomeValue;"

Note the quotation marks. To reference a variable from within SQL you need temporarily "drop-out" of SQL.

"SELECT SomeField FROM tblTable WHERE SomeField= " & VBA_variable & ";"

Additionally, to keep your SQL code readable, a long string such as "[Forms]!

![txtTable].[EmpID]

". Should be placed in a variable.

strFieldName1= "[Forms]![Table]![txtTable].[EmpID]"
strFieldName2= "[Forms]![Table]![txtTable].[TrngID]"
strFieldNames= strFieldName1 & "," & strFieldName2

You can keep expanding the concept. Be careful with the quote marks, you need a space after them in certain situations.

"SELECT " & strFieldNames & " FROM tblTable WHERE SomeField= " & VBA_variable & ";"

PS: After I posted, I see that BigHappyDaddy provided a reply too. He provided a very good suggestion.

 

[cpc]

Appreciate the reply guys. Variables in SQL seems more involved than it looks!