Microsoft Office Tutorials and References
In Depth Information
SQL and Recordsets
first line, delete it. Then press the Delete (Del) key to delete the line break
and bring the next line up to the current line. Leave a blank space between
any whole words. Repeat this process until the entire SQL statement is one
big, long line in the Code window.
Finally, look through the SQL statement for any double quotation marks.
Don’t disturb the quotation marks surrounding the whole SQL statement.
Just change any double quotation marks within the statement
WHERE ((([Address Book].StateProv)=”CA”)) ORDER BY
to single quotation marks:
WHERE ((([Address Book].StateProv)=’CA’)) ORDER BY
When everything is clean, the Code window accepts the statement without
displaying any red lines (lines with code that VBA doesn’t recognize) or
Compile Error messages.
Breaking up long SQL statements
In the preceding section, we say that to make a copied SQL statement work
in your code, you have to treat it as one extremely long line. An alternative
to the one-extremely-long-line approach is to store the SQL statement as a
string variable and then use that variable name in your myRecordset.Open
statement. Within the code, build the lengthy SQL statement by joining short
chunks of text.
The first step is declaring a string variable, perhaps named mySQL , to store
the SQL statement, as the following variable shows:
Dim mySQL As String
Assign the SQL statement to the string. Use the following rules to assign the
✦ Each chunk is fully enclosed in quotation marks.
✦ If a blank space is after a word, leave that blank space in the line.
✦ Follow each line with an ampersand (&) character (the join-strings
operator), a blank space, and the continuation character (_).
✦ Use the variable name in the recordset.Open statement.
You still have to convert any embedded double quotation marks to single
quotation marks and then remove the ending semicolon.