Monday, April 19, 2004

 

Conditional Expressions in JET SQL

AKA “Can I use If … Then or Select … Case in a Microsoft Access query?”

Part One

The short answer to the second form of this question is no, you can’t use If … Then or Select … Case directly in a Microsoft Access query. If your query will only be used within Microsoft Access applications, you can write a VBA (Visual Basic for Applications) function that uses If … Then or Select … Case and call that VBA function from your query. If your query will be used by other applications, you can use the Choose, IIf, or Switch functions, or you can retrieve the data ‘as is’ and manipulate it as necessary in the calling application.

To illustrate this, we need some test data. For the examples that follow, we need an MDB, TEST.MDB, containing a single table, tblTest. The table has just two fields, TestID, an AutoNumber field and the primary key, and TestText, a text field.

We’ll also need a custom VBA function, TestFunc. The function looks like this …

Public Function TestFunc(ByVal lngInput As Long) As String

    If lngInput = 1 Then
        TestFunc = "It's One"
    Else
        TestFunc = "It's not One"
    End If
    
End Function

Now we can start creating our queries. The first query will include only the Choose, IIf, and Switch functions. The SQL view of this query looks like this …

SELECT 
    tblTest.TestID, 
    IIf([TestID]=1,"A","B") AS Expr1, 
    Switch([TestID]=1,"A",[TestID]=2,"B",True,"C") AS Expr2, 
    Choose([TestID],"A","B","C") AS Expr3
FROM tblTest
WITH OWNERACCESS OPTION;

The second query includes the same three functions, but also adds the NZ function, which is defined in the Microsoft Access object library (in other words, it is part of Microsoft Access, not part of VBA or JET) and a call to our custom VBA function. The SQL view of this second query looks like this …

SELECT 
    tblTest.TestID, 
    IIf([TestID]=1,"A","B") AS Expr1, 
    Switch([TestID]=1,"A",[TestID]=2,"B",True,"C") AS Expr2, 
    Choose([TestID],"A","B","C") AS Expr3, 
    NZ(Choose([TestID],"A","B","C"),"D") AS Expr4, 
    TestFunc([TestID]) AS Expr5
FROM tblTest
WITH OWNERACCESS OPTION;

Both of these queries will run without error within our Microsoft Access application. But what happens when we try to use these queries from an application other than Microsoft Access?

For illustration, I’ve used Microsoft Excel, but you will encounter similar results in any other application or development tool, including other VBA-enabled Microsoft Office applications, VB6, or VB.NET or C#.

The Excel document includes two command buttons (on Sheet1) and the following VBA code in Sheet1’s VBA module …

Option Explicit

Public Sub ExecQuery(ByVal strQueryName As String)

    Dim strProvider As String
    Dim strDataSource As String
    Dim strConnectionString As String
    Dim objConnection As ADODB.Connection
    Dim objRecordset As ADODB.Recordset
    
    On Error GoTo ErrorHandler
    strProvider = "Provider = Microsoft.Jet.OLEDB.4.0"
    
    'Change this to the path and file name of the test MDB on your PC.
    strDataSource = "Data Source = C:\Documents and Settings\Brendan Reynolds\My Documents\TEST.MDB"
    
    strConnectionString = strProvider & ";" & strDataSource
    Set objConnection = New ADODB.Connection
    objConnection.ConnectionString = strConnectionString
    objConnection.Open
    Set objRecordset = objConnection.Execute(strQueryName)
    MsgBox objRecordset.Fields(0)
    objRecordset.Close
    objConnection.Close
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly Or vbInformation
    If Not objRecordset Is Nothing Then
        If objRecordset.State <> adStateClosed Then
            objRecordset.Close
        End If
    End If
    If Not objConnection Is Nothing Then
        If objConnection.State <> ADODB.adStateClosed Then
            objConnection.Close
        End If
    End If
    Resume ExitProcedure
    
End Sub

Private Sub CommandButton1_Click()

    ExecQuery "qryTest1"
    
End Sub

Private Sub CommandButton2_Click()

    ExecQuery "qryTest2"
    
End Sub

The code in the ExecQuery procedure executes the query specified in the strQueryName argument, and displays the value of the first field of the first record in the resulting recordset in a message box. The code in the Click event procedures of the two command buttons calls this procedure, passing the query names qryTest1 and qryTest2, respectively.

Clicking the first command button, which passes the query name qryTest1 to the ExecQuery procedure, results in a message box displaying the value of the TestID field in the first record. But clicking the second command button, which passes the query name qryTest2, results in an error message

What’s going on here?

The ability to use Microsoft Access functions and custom VBA functions in queries is a very powerful feature of Microsoft Access. But it is a feature of Microsoft Access, and, although it may not be immediately apparent, when we execute the query from another application or development tool, we are not using Microsoft Access. We are using the Microsoft JET database engine.

Although, from most points of view, Microsoft Access and the JET database engine are very closely related, they are separate and independent entities. You can use Microsoft Access without using the JET database engine (a Microsoft Access ADP application serving as the front-end to a Microsoft SQL Server or MSDE database) and you can use the JET database engine without using Microsoft Access, as we are doing when we execute our queries from VBA code behind a Microsoft Excel workbook. Because we are using the Microsoft JET database engine, not Microsoft Access, we are limited to features supported by the Microsoft JET database engine, and the ability to use Microsoft Access functions or custom VBA functions is simply not one of those features.

In future articles, we’ll delve a little deeper into the question of just what functions can be used with JET queries executed outside the Microsoft Access environment, and the syntax and behaviour of some of those functions.


Send feedback on this post to (change the obvious):

Brendan Reynolds
bwnnfb at hotmail dot com

(For the insatiably curious, "bwnnfb" is an acronym for "Blog With No Name Feed-Back")

Copyright © 2004 Brendan Reynolds. All rights reserved.


Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?