Problem with WHERE syntax!

[mabrynda]

Hello,
In ACCESS 2003, I have a combox with a drop-down menu where I can select a name from a table where one of the fields is FactorName. I'm selecting a name from this field.
I have another table called FactorsCol1 which has 4 fields (including the same FactorName):
ClassName, FactorName, FactorIndex, ClassID

I want to extract a FactorIndex from this table based on information obtained after the choice in my drop down menu. Which means if I select a name say "ABC" I want to exctract the FactorIndex say "005", which is associated with this name. But here is the problem:

I use the following code:

MyVar = Me.FactorSelector1
 
Debug.Print MyVar
 
 DoCmd.RunSQL " SELECT FactorsCol1.FactorIndex, FactorsCol1.FactorName INTO TestTable1 FROM FactorsCol1 WHERE (([FactorsCol1].[FactorName]) = ' & MyVar & ') "
 

MyVar is the name that I get from the field FactorName in my first table. It is always correct because I'm checking it with debug.print. For an unknown reason I get always an empty TestTable1. If however I replace the MyVar directly with the name (say ABC, see the code before) then the SQL is executed and I get the corresponding FactorIndex. Is there something wrong with using variable in this statment?

DoCmd.RunSQL " SELECT FactorsCol1.FactorIndex, FactorsCol1.FactorName INTO TestTable1 FROM FactorsCol1 WHERE (([FactorsCol1].[FactorName]) = ' ABC') "
 

[Stewart Ross]

Hi. You are missing two double quotes - one just after the single quote and before your variable, and one just after. Without them your string cannot include the value of your variable, as you have not split it up into its component parts.

To make it easier to read I have introduced a string variable to hold the SQL string and split it onto separate lines:

Dim strSQL as String
 
strSQL = "SELECT FactorIndex, FactorName " 
strSQL = strSQL & "INTO TestTable1 FROM FactorsCol1 "
strSQL = strSQL & "WHERE [FactorName] = '" & MyVar & "';"
 
DoCmd.RunSQL strSQL

-Stewart

[mabrynda]

Many thanks Stewart.
This definitely solved the problem. Also in another query I was trying to write.

Marcin