Friday, April 23, 2004


Conditional Expressions in JET SQL

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

Part Five

In previous posts, in our investigation into which functions can be used in JET SQL when executed outside the Microsoft Access environment, we covered functions with names beginning with “A” and “C”. (There aren’t any with names beginning with “B”). In this post, we’ll examine those with names beginning with “D”. Of these, the Dir and Dir$ functions failed with “undefined function” error messages when tested in a query within the Microsoft Access environment, and the DoEvents function is one of the functions blocked by the new “sandbox” mode in Access 2003. The following SQL statement includes all of the other functions with names beginning with “D”. With the exception of the DDB (Double-Declining Balance) function, these are all date-related functions.

    Date() AS TestDateVar, 
    Date$() AS TestDateString, 
    DateAdd("yyyy",1,[TestDate]) AS TestDateAdd, 
    DateDiff("d",Date(),[TestDate]) AS TestDateDiff, 
    DatePart("m",[TestDate]) AS TestDatePart, 
    DateSerial(Year(Date()),Month(Date()),[TestLong]) AS TestDateSerial, 
    DateValue("22 April 2004") AS TestDateValue, 
    Day([TestDate]) AS TestDay, 
    DDB(1234.56,123.45,5.5,2.75) AS TestDDB 
FROM tblTest;

“TestDate” and “TestLong” are fields in the table “tblTest”. As the names suggest, their data types are Date/Time and Long Integer, respectively. As always in this series, this rather strange query is not intended to return meaningful information or demonstrate 'best practice', its purpose is simply to investigate whether these functions can be used in a query when executed outside of the Microsoft Access environment.

When run through our test C# console application, “JetTest” (see previous posts) the query executed without error, and returned the same results as when opened in datasheet view in Microsoft Access.

So we can now add the following functions to our list of functions that can be used in JET queries executed outside the Microsoft Access environment:

Date, Date$, DateAdd, DateDiff, DatePart, DateSerial, DateValue, Day, and DDB.

If you look closely, you’ll notice that we can add the Year and Month functions, too.

Plans for future posts (as always, in no particular order and in no specific time-frame) include the following …

Explain the syntax and use of the remaining conditional functions, IIf and Switch. Enhance the JetTest utility and perhaps provide VB.NET and/or VBA versions. Explain how to build the utility for those who may not be familiar with VS.NET.

For the next several posts, I’ll alternate between the list of functions and the other subjects. As this post was all about the list of functions, the next post will be devoted to one of the other subjects (probably the JetTest utility), the one after that will return to the list of functions, and so on until we get through the list.

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?