Question:
I have been having a lot of trouble with a SqlLookup() function. Whenever I use a WHERE clause, I get a parse error.
The offending WHERE clause is “WHERE SW_LICENSE = ” & SWL_NUMBER.Value
. The SW_LICENSE field is a VARCHAR datatype. Help!
Answer:
The problem comes from an incorrect SQL statement. The SW_LICENSE field is a character field, but, for a value of “25”, the WHERE clause parses out to WHERE SW_LICENSE = 25
. Since the column is expecting a string, the parsed WHERE clause should be WHERE SQL_LICENSE = ’25′”
, and the WHERE clause should read “WHERE SW_LICENSE = ‘” & SWL_NUMBER.Value & “‘”
.
It’s can be dangerous to give your objects or columns misleading names, such as calling a string field “SWL_NUMBER”.