Syntax Error - Unknown cause, can anyone help?

[rosdancer]

When I run the following code I get this error Syntax error (missing operator) in query expression 'validation='countryCode' AND'.
I'm not sure what the problem is and therefore I'm not sure how to change this code.
Thanks in advance for the help.

Roslyn

Private Sub Form_Load()
lngCurrentID = 1
strSql = "SELECT * FROM ValidationList WHERE fldvalidSection=" & SQLText("countryCode&quot & " AND order=" & SQLNumber("1&quot
countryCodes.Open strSql, gConnection, adOpenStatic, adLockOptimistic
lockCountryCode
If countryCodes.BOF Or countryCodes.EOF Then
MsgBox "There are no countries please add one.", vbOKOnly, "Country Maintenance - VisualToPowership"
txtCountry = ""
txtDescription = ""
countryCodes.Close
unlockCountryCode
Else
countryCodes.Close
displayRecord
End If
End Sub

 

[harebrain]

I don't know what the SQLText function is, but I'll bet you should omit the quotes from the argument, i.e., use

SQLText(countryCode)

in your built-up strSQL.

 

[Transcend]

What you could also do is stop your code at this line

strSql = "SELECT * FROM ValidationList WHERE fldvalidSection=" & SQLText("countryCode&quot & " AND order=" & SQLNumber("1&quot

step over that line, then see what the value of strSQL is

Transcend

 

[CassandraR]

Hi harebrain:

I was curious about the SQLText function. A description is found at

http://www.pbdr.com/vbtips/gen/sqltext.htm

Cassie

 

[rosdancer]

Thanks for the help guys, I figured out what is was,

order is special word in SQL, can't use it as a variable.

SQLText and SQLNumber change strings into text and number that are translated into readable code.

Also harebrain, countrycode is a string not a variable containing a string, so it needs the quotes.

Roslyn

 

[harebrain]

Roslyn,

Well, that took all the fun out of my wild guess! Glad you figured it out! Although I can't quite make sense of why, if countrycode is a literal, you'd build the strSQL as you did instead of just putting it all together in one string, e.g.,

strSql = "SELECT * FROM ValidationList WHERE fldvalidSection = 'countryCode' AND ..."

Nevermind that string concatenation is inefficient, you just made extra work for yourself.


Cassie,

Thanks for the pointer to the SQLText function. Cool! I don't think it's what Roslyn was using, though, as the signature is different (two args rather than one.) I'm snagging it for myself. Not that I haven't created a similar function, but somebody actually documented the one you found! ;-)

 

[rosdancer]

Good point, I don't usually use an actual string I ususally use a variable. Thanks for the suggestion.

Roslyn