Using SQL Strings from within VBA

Using SQL Strings from within VBA

This can appear quite confusing at first. If you are using SQL Strings from within VBA, for example to open a Recordset, or to perform action queries without using a saved query, then you will need to be aware of several points.

How to Handle Different Datatypes

A simple SQL string might be:

strSQL="SELECT * FROM [tblName] WHERE [Field1]=5;"

This says 'select all data for all records from a table called tblName where a field called Field1 equals 5'. This is using a numeric criteria, where you don't need to worry about wrapping the data in special characters. If, however, you are using a text value as a criteria, then you will need to wrap it in single quotes, as below:

strSQL="SELECT * FROM [tblName] WHERE [Field2]='Smith';"

The next problem that might arise is if the criteria itself has a single quote in, for example d'Abo. In this case, you can use the following:

Const strQuote=""""
strSQL="SELECT * FROM [tblName] WHERE [Field2]=" & strQuote & "D'Abo" & strQuote

Finally, if you are using dates within the SQL statement, then you need a different special character to indicate the datatype, namely #:

strSQL="SELECT * FROM [tblName] WHERE [Field3]=#01/01/2001#;"

The other catch with dates is that when the SQL is interpreted, the date is expected to be in am American format, i.e. 'mm/dd/yyyy', which may be different to your local settings. In this case, you will need to ensure that when the date is passed in, it cannot be taken as anything apart from the date that you expect:

strSQL="SELECT * FROM [tblName] WHERE [Field3]=Format(#01/01/2001#,'mm\/dd\/yyyy');"

How to Handle Variables in SQL Strings

In order for SQL to be aware of variables in the string, whether they be controls on a form or VBA variables, you must force them to be evaluated. You achieve this by concatenating the variable into the SQL string. For example, you might have a query whose SQL is:

SELECT tblName.*
FROM tblName
WHERE (((tblName.Field1)=[Forms]![frmMain]![txtField1]));

As DAO has no idea of the value from this text field when it comes to execute the statement, it needs to be rewritten as:

strSQL="SELECT * FROM [tblName] WHERE [Field1]=" & Forms!frmMain!txtField1 & ";"

or:

strSQL="SELECT * FROM [tblName] WHERE [Field2]='" & strVariable & "';"

Again, you will need to use the correct characters to wrap the variable in if required.

Other Tips

If you have Field or Table names with spaces in, or even reserved characters, then you get around this in Access by wrapping the name in square brackets, for example:

strSQL="SELECT * FROM [Stock Table] WHERE [Field1]=4;"

Of course, a better option is to not create such names in the first place!

Another thing that I will do, in order to make debugging easier, is to create a String variable, strSQL, that I use to store the SQL statement in. This makes it far easier to troubleshoot what is happening, either by using Debug.Print to output to the Immediate Window, or else to use MsgBox to display it.

Finally, if you are dealing with a very long SQL string, and find it difficult to view it on screen, there are several ways to spread the SQL over several lines:

strSQL="SELECT * FROM [tblName]"
strSQL=strSQL & " WHERE [Field1]=5;"

Or:

strSQL="SELECT * FROM" _
& " WHERE [Field1]=5;"

The important thing here is to ensure that you leave a space at either the end of beginning of each line, otherwise, when the lines are joined back together, it might read something like this:

strSQL="SELECT * FROM [tblName]WHERE [Field1]=5;"

If in doubt, leave a space at both the end of a line and the beginning of the next line, as Access will quite happilyread the double space as a single space.

To help you, I have created an Add-In for Access 97 and 2000 that allows you to format a previously saved query and use that in a VBA statement. You can download it from this page.

Top

Last modified at 06/06/2006 13:55:45