I have probably asked already but trying again....(Access VBA)[Solved]

[Besoup]

Hey,
I have a table with 30 fields that I would like to query from using a variable as the criteria. Is this possible in Access VBA? I try to redefine the query but I get syntax errors because of the length of the SQL Statement.

Any input is appreciated...

[salvelinus]

Dim strSQL as String, strVariable as String
 
strSQL = "SELECT fldOne, fldTwo, fldThree " & _
"FROM tblBesoup " & _
"WHERE fldThree = '" & strVariable & "'"

There are single quotes surrounding strVariable after the = sign: - a single quote, double quote, space, ampersand, space, strVariable, space, ampersand, space, double quote, single quote, double quote.
You need the single quotes for strings, the # sign for dates, and nothing for numbers. Instead of strVariable, you could use txtTextBox.Text if the value was in a textbox control, for example, but I prefer using variables.

[RobDog888]

If the length of the sql statement is an issue, you can alais the table and field names to make it shorter.

SELECT ([FieldNameTest]) As F1, ([Someotherfieldname]) As F2 FROM MyTestingTable1 As T1 WHERE F1 = " & SomeVariable & ";"

[Besoup]

Got it running using lots of & _, thanks for the help...