Access VBA and SQL languages

Working together in Access

<- Back

In this example, a VBA command opens a report using SQL to process
the table data that will be displayed on that report :

Note that this code is executed ‘interactively’
with the user while the program is running.

This means that each time it runs, the value for cboDept may be different,

depending on which department the user selects from the cboDept combo box,
(remember that each department is associated with a department number)
but the program will still follow these same instructions
just using different data each time :

' Allocate two string variables ( ** this is not required – see below):
Dim strWhere, strSQL

' Assign an SQL criteria to one of the strings (Notice the single & double quotes):

strWhere = “DeptNo = “ & “'” & cboDept & “'”

'Transfer text string from one variable to another :
' ( ** This step simply copies the contents of strWhere to strSQL. ** )

strSQL = strWhere ‘ Store the criteria in the variable strSQL **

' Use the text string SQL criteria in the VBA command to open a report :

DoCmd.OpenReport “rptEmployeeList”, acViewPreview, strSQL

** Alternate code **

' ** Please note that it was not required to assign strSQL = strWhere

' The string variable strWhere could have been used in the “OpenReport” statement directly :

DoCmd.OpenReport “rptEmployeeList”, acViewPreview, strWhere

This example only shows part of the code, and therefore
it may not be clear just why you might use two variables.
However, using two variables does not change the logic.

Here VBA stores text data in memory in a location called strWhere.

The text data that gets stored in strWhere is an SQL “where” criteria clause :

DeptNo = ’20’

Where did the “20” come from ?

While the program is running, the user selected “Engineering”
from the combo box drop-down list.

“Engineering” is department code 20,

so, selecting stores the associated value 20 :

(Remember, the “DeptNo” field is the “Primary Key” and uniquely identifies each department.)

The value of cboDept would be 60 if the user had selected ,
otherwise the program behaves the same way.

strWhere = “DeptNo = “ & “'” & cboDept & “'”

Now to untangle the quotes that are used here :

If you decipher the concatenations in this VBA statement you will see that
the string DeptNo = is concatenated with an apostrophe ‘ and then with,
and this is important,
the CURRENTLY SELECTED VALUE from the cboDept combo box.

A final apostrophe ‘ is added to the end of the SQL string.

This results in the SQL clause : DeptNo = ’20’

being assigned to the VBA string variable
using the following assignment statement :

strWhere = “DeptNo = ’20’”

Note how the combo box’s value becomes delimited by single-quotes,
because it is a number stored as a text string:[tblDepartments].[Dept No],
and the whole SQL clause (DeptNo = ’20’) is delimited by double-quotes.

The single-quotes are specified in the statement
as part of the Visual Basic text string,
while the value to put between them has to be ‘looked up’
and retrieved from the current combo box selection.

This entire string, including the single quotes (apostrophes)
is stored in a VBA text string using the assignment statement :

strWhere = “DeptNo = “ & “'” & cboDept & “'”

The hard part here is to realize that VBA
looks up the current value selected from the combo box,
such as for example, “20” and inserts the value that it finds there
into the string, and not the characters cboDept.

strWhere = “DeptNo = ’20’”

The program then transfers the text string to
another string variable using an assignment statement :

strSQL = strWhere

VBA then uses the criteria from the SQL where clause embedded
in the VBA “DoCmd” statement to open the
report in “Print Preview” mode (acViewPreview) :

DoCmd.OpenReport “rptEmployeeList”, acViewPreview, strSQL

The report will then be displayed in “Print Preview” mode,
showing only those employees that work in department 20
because that was the selection made from the combo box.

Note about the above VBA code :

The following concatenation is actually unnecessary :

“DeptNo = “ & “'”

It is exactly the same as this :

“DeptNo = '”

Since concatenation simply connects two strings, it could just be typed that way.

It was done this way to emphasize the way single-quotes are enclosed in double-quotes.

<- Back