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:
شركة تنظيف بالرياض 0506003079
عملية التنظيف من العمليات الشاقة لدي المرأة العاملة ومن يكون معها أطفال يكون أمر التنظيف صعب للغاية، لذا يلجأ العديد من السيدات إلى شركة تنظيف بالرياض، فشركة تنظيف بالرياض تقوم بعمل جميع أعمال التنظيف من تنظيف فلل وبيوت ومنازل كما يوجد قسم خاص في الشركة لتنظيف السجاد والموكيت، ويوجد قسم أخر لتنظيف الحمامات والمطابخ فلكل غرفة في المنزل يوجد لها قسم خاص لأنهم يستخدمون مساحيق تنظيف معينه لكل قطعة في المنزل، شركة تنظيف شقق بالرياض لها العديد من الفروع في الكثير من مدن المملكة العربية السعودية

شركة تنظيف بالبخار بالرياض
شركة تنظيف بالرياض
شركة تنظيف مجالس بالرياض
شركة تنظيف مساجد بالرياض
شركة تنظيف شقق بالرياض
شركة تنظيف فلل بالرياض

 
شركة الأوائل توب لتنظيف بالرياض 0506003079
يوجد في شركتنا العديد من المميزات التي تميزها عن غيرها من الشركات، فهدف الشركة الأول هو الدقة والجودة وليس النقود، فيوجد العديد من المميزات
شركة تنظيف موكيت بالرياض
شركة تنظيف منازل بالرياض
شركة تلميع سيراميك بالرياض
شركة تنظيف مدارس بالرياض
تنظيف بالرياض
 
شركة تنظيف خزانات بالرياض 0506003079
لأن الخزانات تعتبر من أكثر الأغراض المستخدمة في كل مكان لذا يجب الاهتمام بنظافتها بشكل دائم لضمان سلامة المياه التي سوف تخزن بها ونرى أن كثيراً من الناس لا يهتمون بتنظيف الخزانات ولكنهم يبحثون عن أشخاص أخرى للقيام بهذه المهام وفي أغلب الأحيان لا يتم التنظيف بشكل كامل لذا يجب الاعتماد على شركة تنظيف خزانات بالرياض التي تتبع أفضل الطرق من أجل الحفاظ على نظافة الخزانات وسلامة المياه.

شركة تسليك مجاري بالرياض
شركة كشف تسربات المياه بالرياض
شركة تنظيف بيارات بالرياض
شركة جلي بلاط بالرياض
شركة تنظيف خزانات بالرياض
شركة عزل فوم بالرياض
شركة عزل اسطح بالرياض
شركة عزل خزانات بالرياض

 
شركة نقل عفش بالرياض 0506003079
مشكلة نقل العفش من المشاكل التي تواجه الكثير من الناس، خاصة إذا كانوا متعددون السكن، لذا فهي مشكلة لابد من إيجاد حل مثيل لها فمع شركة نقل اثاث بالرياض يمكنك نقل الأثاث بسهولة فائقة بدون حدوث أي أعمال تخريب أو تكسير لقطع الأثاث، كما أن نقل أثاث تعد من الأمور المعتاد عليها الناس، لذا لابد من التعامل مع شركتنا لأن من أفضل الشركات التي تعمل في هذا المجال وتعمل في هذا المجال بمهارة فائقة للغاية، وتستخدم أفضل المعدات والأجهزة والعربات المخصصة لنقل العفش دون حدوث أي غدش في أي قطع أثاث أو حدوث تكسير للأنتيكات أو قطع الزجاج.


شركة شراء اثاث مستعمل بالرياض
شركة نقل اثاث بالرياض
شركة تخزين عفش بالرياض
شركة نقل عفش بالرياض
شركة صيانة مكيفات بالرياض
شركة مقاولات عامة بالرياض


 
شركة مكافحة حشرات بالرياض 0506003079
الحشرات تزعج الناس حتى وأن كانت حشرات طائرة أم حشرات زاحفة، لذا يجب علينا التخلص من الحشرات نهائياً من المنزل لانها تسبب الكثير من الأمراض خاصة للأطفال، فيوجد أنواع كثيرة من الحشرات من الممكن أن تسكن في المنزل فيوجد حشرة البق تسكن في المفروشات والمراتب والتخلص منها ليس بسهولة، ويوجد نمل وصراصير وهؤلاء الحشرات تسكن المطابخ بكثرة وذلك لكثرة القمامة وعدم التخلص منها بأستمرار، كما يوجد نوعين من الحشرات يوجد حشرات زاحفة مثل الفئران والنمل وبعض أنواع الصراصير ويوجد حشرات طائرة مثل الذباب والبعوض وبعض أخر من الصراصير، فلابد على كل منزل عند رؤية أي نوع من هذه الحشرات فلابد بالاتصال على شركتنا على الفور شركة رش مبيدات بالرياض.


مكافحة حشرات بالرياض
شركة رش مبيدات بالرياض
شركة مكافحة فئران بالرياض
شركة مكافحة صراصير بالرياض
شركة مكافحة النمل الابيض بالرياض
شركة مكافحة حشرات بالرياض
شركة مكافحة البق بالرياض


 
Post a Comment

<< Home

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