SQL Expressions

Description

An SQL expression is a string that makes up all or part of an SQL statement. For example, the FindFirst method on a Recordset object uses an SQL expression consisting of the selection criteria found in an SQL WHERE clause.

The Microsoft Jet database engine uses the Visual Basic for Applications (or VBA) expression service to perform simple arithmetic and function evaluation. All of the operators used in Microsoft Jet SQL expressions (except Between, In, and Like) are defined by the VBA expression service. In addition, the VBA expression service offers over 100 VBA functions that you can use in SQL expressions. For example, you can use these VBA functions to compose an SQL query in the Microsoft Access query Design view, and you can also use these functions in an SQL query in the DAO OpenRecordset method in Microsoft Visual C++, Microsoft Visual Basic, and Microsoft Excel code.

Specifics (Microsoft Access)

Many Visual Basic functions can be used in SQL strings. You can use Visual Basic functions in SQL strings included in Visual Basic code, in SQL view of the Query window, or in the query design grid. You can also write your own Visual Basic functions and use them in SQL strings.

Functions that can be included in SQL strings must return a value that is either a string or a Variant. Also, whatever operation is performed by the function can be performed only once. You can't include a function that must be performed on every record in the data set, because the SQL statement is only passed to the Microsoft Jet database engine once. For example, you can't create a function that takes a field value as an argument.

You can include Visual Basic functions in an SQL statement that you use to define a QueryDef object, or a dynaset-type or snapshot-type Recordset object in code. In the Microsoft Access query design grid, you can include Visual Basic functions in an SQL statement, in criteria expressions, or in calculated field expressions.

Example (Microsoft Access)

The following example creates a dynaset-type Recordset object from an SQL statement. The SQL statement includes the Year function in a WHERE clause to return only records for orders placed in 1995.

Sub Orders96()
    Dim dbs As Database, rst As Recordset, strSQL As String
    Dim fld As Field

    Set dbs = CurrentDb
    strSQL = "SELECT DISTINCTROW OrderID, OrderDate " _
        & "FROM Orders WHERE ((Year([OrderDate])=1996));"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    rst.MoveLast
    Debug.Print rst.RecordCount
End Sub

The next example shows how you can include a Visual Basic function in an SQL statement in SQL view in the Query window. The following SQL statement defines a query that displays the ShipName field from an Orders table, calculates the number of characters in that field for each record by using the Len function, and displays that calculation in another column.

SELECT DISTINCTROW ShipName, Len([ShipName]) AS LengthOfShipName
FROM Orders;

You can create the same query in the query design grid. Create a new query and add the Orders table. Drag the ShipName field onto the Field cell in the first column in the grid. In another Field cell, create a calculated field expression by entering the following expression.

LengthOfShipName: Len([ShipName])