Monday, April 19, 2004
Conditional Expressions in JET SQL
AKA “Can I use If … Then or Select … Case in a Microsoft Access query?”
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):
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.