too few parameters, expected 1

[evaleah]

I have a VB6 app running against an Access2000 database. I have the following bit of code to generate a SQL statement:

strSQL = "select distinct QCType, LowValue, HighValue from " & strSpecsTable
    strSQL = strSQL + " where ShortName = """ & FieldName & """"

When this is run it produces a perfect SQL statement. It looks exactly like what I am looking for. The value coming up in the Immediate window is:
Code:

select distinct QCType, LowValue, HighValue from tblDataSpecs_v1_0 where ShortName = "CRITERIA"

I copy and paste this directly into the Access DB and it runs beautifully. I have checked my connection string, that is all perfect and running against the correct database. Still, I am getting "Too few parameters. Expected 1" each and every time I allow the program to execute this statement. What am I missing?

HELP!!!! Thanks!

 

[evaleah]

Turns out if I altered the statement to use ' instead of " it works.

 

[Andrzejek]

Did you try this:

strSQL = "select distinct QCType, LowValue, HighValue from " & strSpecsTable
strSQL = strSQL + " where ShortName = " & Chr(34) & FieldName & Chr(34)

Have fun.

---- Andy

 

[Andrzejek]

Of course, single quote.....

Have fun.

---- Andy