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 expressions operators used in Jet database engine 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 can be used in SQL expressions. For example, an SQL query composed in the SQL View of the Microsoft Access Query Design window can use these VBA functions, and so can an SQL query used in the DAO OpenRecordset method in Microsoft Visual C++™, Microsoft Visual Basic, and Microsoft Excel code.

Specifics (Microsoft Access)

You can use many Visual Basic functions in SQL strings while in Visual Basic code, in SQL view of the Query window, or in the query design grid.

For example, 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 the Microsoft Access query design grid, you can include Visual Basic functions in criteria expressions, or in a calculated field expression.

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 Orders95()
    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])=1995));"
    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 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])