SQL "WHERE" clause errors when string variables used

[EagleOne]

2003

The following command when executed, works fine:

dBs.Execute "INSERT INTO CHOOSEOffsets SELECT TOP 1 * FROM CHOOSERev" & _
" WHERE " & NextActualAmt & " + " & StartActualAmt & " = 0 "

But if I attempt:

dBs.Execute "INSERT INTO CHOOSEOffsets SELECT TOP 1 * FROM CHOOSERev" & _
" WHERE " & NextDovNmbr & " = " & StartDovNmbr



The ERROR is 3075 Syntax error (missing operator) in query expression 'ABC = ABC'

NOTE: NextActualAmt and StartActualAmt are Dimmed as Long

NextDovNmbr and StartDovNmbr are Dimmed as String (values both "ABC")

There appears to be a syntax difference in the WHERE clause Numeric vs String?

The full clause that I wish to perfect is:

dBs.Execute "INSERT INTO CHOOSEOffsets SELECT TOP 1 * FROM CHOOSERev" &
" WHERE " & NextActualAmt & "+" & StartActualAmt & " = 0 And " & StartActualAmt & " <= 0 And ((" & _
NextDovNmbr & "=" & StartDovNmbr & ") Or (" & NextDocNmbr & "=" & StartDocNmbr & "))"

If I attempt the full clause I get the same error. I simplified the clause above to isolate the
cause of the error

Any help appreciated.

TIA EagleOne

 

[Douglas J. Steele]

Text values need to be enclosed in quotes. You need 'ABC' = 'ABC', not ABC =
ABC.

dBs.Execute "INSERT INTO CHOOSEOffsets SELECT TOP 1 * FROM CHOOSERev" & _
" WHERE '" & NextDovNmbr & "' = '" & StartDovNmbr & "'"

Exagerated for clarity, that's

dBs.Execute "INSERT INTO CHOOSEOffsets SELECT TOP 1 * FROM CHOOSERev" & _
" WHERE ' " & NextDovNmbr & " ' = ' " & StartDovNmbr & " ' "

Note that that will fail, though, if NextDovNmbr or StartDovNmbr contains an
apostrophe. If you know that the values might contain an apostrophe, but
will never include double quotes, use


dBs.Execute "INSERT INTO CHOOSEOffsets SELECT TOP 1 * FROM CHOOSERev" & _
" WHERE """ & NextDovNmbr & """ = """ & StartDovNmbr & """"

That last line has three double quotes in a row after WHERE, three double
quotes before and after the equal sign, and four double quotes at the end.

If there's a chance that you'll have both apostrophes and double quotes in
the values, see my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html

 

[EagleOne]

Never in a million would I gave guessed the answer.

Your answer is saved for my time in my growing Access Bible.

Thanks