Friday, September 17, 2004

 

Functions With Names Beginning With S

Of the VBA functions with names beginning with the letter 'S', there are two which are blocked in 'sandbox' mode, and two which raised an 'undefined function' error when used in a query within the Microsoft Access environment.

The two functions that are blocked in 'sandbox' mode are 'seek' and 'shell'. The two that failed with 'undefined function' errors are 'Spc' and 'Split'. It is no surprise that these two failed, as the Spc function is intended for use with the VBA 'Print' statement, and the Split function returns an array.

The remaining functions with names that begin with the letter 'S' and executed without error in a query executed within the Microsoft Access environment are: Second, Sgn, Sin, SLN, Space, Sqr, Str, Str$, StrComp, StrConv, StrReverse, String, String$, Switch, and SYD.

Here's the SQL for the query I used to test these functions within the Microsoft Access environment:

SELECT 
    Second([TestDate]) AS TestSecond, 
    Sgn([TestInteger]) AS TestSgn, 
    Sin([TestDouble]) AS TestSin, 
    SLN([TestDouble],[TestDouble]/10,[TestLong]) AS TestSLN, 
    Space([TestLong]) & "|" AS TestSpace, 
    Sqr(Abs([TestDouble])) AS TestSqr, 
    Str([TestDouble]) AS TestStr, 
    Str$([TestDouble]) AS TestStrS, 
    StrComp([TestText],"m") AS TestStrComp, 
    StrConv([TestText],1) AS TestStrConv, 
    StrReverse([TestText]) AS TestStrReverse, 
    String([TestLong],[TestText]) AS TestString, 
    String$([TestLong],[TestText]) AS TestStringS, 
    Switch([TestInteger]>0,"Positive",
        [TestInteger]<0,"Negative",
        [TestInteger]=0,"Zero",
        [TestInteger] Is Null,"Null") AS TestSwitch, 
    SYD([TestDouble],[TestDouble]/10,[TestLong],[TestLong]\2) AS TestSYD
FROM tblTest
WITH OWNERACCESS OPTION;

Just one function, the StrReverse function, failed when the query was executed outside of the Microsoft Access environment, via our C# JetTest utility (see earlier posts). The removal of that column enabled the query to execute without error via JetTest also. So we can now add the following functions to our list of VBA functions that can be used in Jet queries executed outside of the Microsoft Access environment:

Second, Sgn, Sin, SLN, Space, Sqr, Str, Str$, StrComp, StrConv, String, String$, Switch, and SYD.


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.


Tuesday, September 07, 2004

 

Functions With Names Beginning With Q and R

There is only one VBA function with a name beginning with the letter ‘Q’, the QBColor() function. Most of us will probably never have occasion to use this function in a query, but if the occasion ever arises, you can rest assured that it works! :-)

The list of VBA functions with names beginning with the letter ‘R’ is a lot longer: Rate, Replace, RGB, Right, Right$, RightB, RightB$, Rnd, Round, RTrim, and RTrim$.

All of these functions executed without error in a query executed within the Microsoft Access environment. Here’s the SQL for the query that worked with Microsoft Access:

SELECT
    QBColor([TestByte]) AS TestQBColor, 
    Rate([TestDouble1],-[TestDouble2],[TestDouble3]) AS TestRate, 
    Replace([TestText],"e","x") AS TestReplace, 
    RGB([TestByte],[TestInteger],[TestLong]) AS TestRGB, 
    Right([TestText],3) AS TestRight, 
    Right$([TestText],3) AS TestRightS, 
    RightB([TestText],3) AS TestRightB, 
    RightB$([TestText],3) AS TestRightBS, 
    Rnd([TestByte]) AS TestRnd, 
    Round([TestDouble],2) AS TestRound, 
    RTrim([TestText]) AS TestRTrim, 
    RTrim$([TestText]) AS TestRTrimS
FROM tblTestWITH OWNERACCESS OPTION;

Just one function, the Replace function, failed when the query was executed outside of the Microsoft Access environment, via our C# ‘JetTest’ utility (see earlier posts). The removal of that column enabled the query to execute without error via ‘JetTest’ also. So we can now add the following functions to our list of VBA functions that can be used in Jet queries executed outside of the Microsoft Access environment:

QBColor, Rate, RGB, Right, Right$, RightB, RightB$, Rnd, Round, RTrim, and RTrim$.


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.


Wednesday, June 09, 2004

 

Functions With Names Beginning With P

The problem with a series of posts like this one is that things only get interesting when something goes wrong. A post that says ‘I tested this, and it works, and I didn’t encounter any problems’ isn’t really very interesting, and that’s exactly what happened today. Not to worry, I will persevere, in the confident expectation that something will go wrong real soon now! :-)

There are four VBA functions with names beginning with the letter ‘P’, the Partition(), PMT(), PPMT() and PV() functions. Here’s the SQL for a query that uses all four of these functions. As always in this series, this rather strange query is not intended to return a meaningful result, it is merely a test bed for the functions:

SELECT 
    Partition([TestInteger],1,10,2) AS TestPartition, 
    Pmt([TestDouble],[TestInteger],[TestDouble],[TestCurrency],1) AS TestPmt, 
    PPmt([TestDouble],[TestInteger],[TestInteger],[TestDouble],[TestCurrency],1) AS TestPPmt, 
    PV([TestDouble],[TestInteger],[TestDouble],[TestCurrency],1) AS TestPV 
FROM tblTest;

As indicated above, this query executed with a boring lack of problems both within Microsoft Access and via our C# ‘JetTest’ utility (see previous posts). So we can now add these four functions to our list of VBA functions that can be used in Jet queries when executed outside of the Microsoft Access environment:

The Partition(), PMT(), PPMT() and PV() 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.


Tuesday, June 08, 2004

 

Functions With Names Beginning With O

First, my apologies for the long, unannounced hiatus in posting – I had a one week holiday, and then it took me another week to get caught up on all the things that piled up during the holiday, you know how that goes!:-)

Continuing our alphabetical investigation of VBA functions that can be used in Jet queries, there are just two functions (or two variations of one function) with names beginning with the letter ‘O’, the Oct() and Oct$() functions. Both of these functions work as documented in queries both within Access and when executed via our C# ‘JetTest’ utility (see previous posts). Here’s the SQL statement for a query that uses these two functions:

SELECT 
    Oct([TestInteger]) AS TestOctVar, 
    Oct$([TestInteger]) AS TestOctString 
FROM tblTest;

So we can add these two functions to our list of VBA functions that can be used in Jet queries when executed outside the Microsoft Access environment:

The Oct() and Oct$() 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.


Tuesday, May 18, 2004

 

Functions With Names Beginning With N

There are just three VBA functions with names beginning with N, the Now, NPer, and NPV functions. The NPV function takes an array as its second argument, and like all of the functions involving arrays that we have tested so far, it proved unusable in a query executed within the Microsoft Access environment. The following SQL statement includes the remaining two functions, Now and NPer:

SELECT 
    Now() AS TestNow, 
    NPer(1.2,2.3,3.4) AS TestNPER 
FROM tblTest;

This query executed without error both within the Microsoft Access environment and via the JetTest utility, so we can add these two functions to our list of functions that can be used in Jet queries executed outside of the Microsoft Access environment:

Now and NPer.


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.


Monday, May 17, 2004

 

Functions in Microsoft Jet Queries

Functions with Names beginning with M

The MacID and MacScript functions are only used on the Apple Macintosh, and the MIRR and MsgBox functions both failed when used in a query within the Microsoft Access environment. The following SQL statement includes all of the functions with names beginning with M that executed without error within the Microsoft Access environment:

SELECT 
    Mid([TestText],Len([TestText])\2) AS TestMid, 
    Mid$([TestText],Len([TestText])\2) AS TestMidS, 
    MidB([TestText],Len([TestText])\2) AS TestMidB, 
    MidB$([TestText],Len([TestText])\2) AS TestMidBS, 
    Minute(Now()) AS TestMinute, 
    Month(Now()) AS TestMonth, 
    MonthName(Month(Now())) AS TestMonthName 
FROM tblTest;

When executed via JetTest, only the MonthName function failed. The following, then, is the list of functions with names beginning with M that can be added to our list of functions that can be used in Jet queries executed outside of the Microsoft Access environment:

Mid, Mid$, MidB, MidB$, Minute, and Month.


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.


Friday, May 14, 2004

 

Functions in Microsoft Jet Queries

Functions with names beginning J to L

The only VBA function with a name beginning with J is the Join function, and this – like all of the array-related functions tested so far – failed with an undefined function error message when tested in a query within the Microsoft Access environment.

There are no VBA functions with names beginning with K.

Of the functions with names beginning with L, the LBound function (another array-related function) failed with the now familiar undefined function message, and the Loc and LOF functions are blocked in sandbox mode.

Here’s the SQL for a query that includes all of the other functions with names beginning with L:

SELECT 
    LCase([TestText]) AS TestLCase, 
    LCase$([TestText] & "") AS TestLCaseString, 
    Left([TestText],1) AS TestLeft, 
    Left$([TestText] & "",1) AS TestLeftString, 
    LeftB([TestText],2) AS TestLeftB, 
    LeftB$([TestText] & "",2) AS TestLeftBString, 
    Len([TestText]) AS TestLen, 
    LenB([TestText]) AS TestLenB, 
    Log([TestDouble]) AS TestLog, 
    LTrim([TestText]) AS TestLTrim, 
    LTrim$([TestText] & "") AS TestLTrimString 
FROM tblTest;

As an aside, note that I’ve appended an empty string (two quotes with nothing between them) to the parameter for each of the functions that ends with a $ symbol. These functions return a string (unlike the versions without the $ symbol, which return a variant). As a string can not contain a Null value, without the empty string errors would have occurred when the value of the field used as the parameter for these functions was Null. The concatenation of the empty string coerces the value to a string, avoiding those errors.

The above query ran without error, both within the Microsoft Access environment, and when executed via the C#/ADO.NET JetTest utility (see earlier posts) so we can now add the following functions to our growing list of VBA functions that can be used in Microsoft Jet queries executed outside of the Microsoft Access environment:

LCase, LCase$, Left, Left$, LeftB, LeftB$, Len, LenB, Log, LTrim, and LTrim$.


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.


Friday, May 07, 2004

 

Functions in Microsoft Jet Queries

The IIf (Immediate If) Function

The IIf function takes three arguments, referred to in the help files as “expr”, “truepart”, and “falsepart”. The function returns the value of the “truepart” argument if the “expr” (expression) argument evaluates to True. It returns the value of the “falsepart” argument if the “expr” argument evaluates to False.

For example, suppose we have a field (TestText in this example) in a table (tblTest in this example) that may contain Null values. What we’d like to display to the user is the value of the field if it is not Null, or the text “N/A” if the value of the field is Null. The following query will achieve this goal:

SELECT IIf([TestText] Is Null,"N/A",[TestText]) AS TestIIf FROM tblTest;

Microsoft Access has an NZ function that can achieve the same goal more simply:

SELECT NZ([TestText],"N/A") AS TestNZ FROM tblTest;

However, the NZ function is implemented in the Microsoft Access object library, not in the VBA expression service, which means that it can only be used within the Microsoft Access environment. The IIf function is implemented in the VBA expression service, and the Jet database engine “knows” how to call functions in the VBA expression service even when the query is executed outside of the Microsoft Access environment.

In addition, the NZ function is a specialised function, dedicated to the purpose of handling Null values. The IIf function is a much more flexible general-purpose function that can handle a much wider range of conditions. For example, the following query will display the letter “A” if the field “TestInteger” contains a value less than or equal to 3, otherwise it will display the letter “B” (the kind of thing you might want to do when grading test scores, for example):

SELECT IIf([TestInteger]<=3,"A","B") AS TestIIf FROM tblTest;

Interestingly enough, the query above displays the letter “B” if the “TestInteger” field contains a Null value. Under most circumstances, “Nulls propagate”, in other words, if any part of an expression is Null, the result of the expression will be Null. This is because Null represents an unknown value, and if any of the elements of an expression are unknown, then the result must also be unknown. In the example above, if the value of the field “TestInteger” is unknown, how can we determine whether the value is less than or equal to any other value? The answer is unknown, in other words, Null. The IIf function, however, can only return one of two values, the values of the “truepart” and “falsepart” arguments. It doesn’t have a “neither of the above” option! It has to evaluate the “expr” argument as either True or False, it does not have the “option” of deciding that the expression should be evaluated as Null.

Handling Null values provides us with one example of why we might use “nested” IIf functions – one or more IIf functions within another. Building on our previous example, suppose we want to distinguish between values that are less than or equal to 3, values that are greater than 3, and Null (unknown) values? The following query, using nested IIf functions, will do that job for us:

SELECT IIf([TestInteger] Is Null,"N/A",IIf([TestInteger]<=3,"A","B")) AS TestIIf FROM tblTest;

If the field “TestInteger” contains a Null value, this expression will display the text “N/A”. If the field “TestInteger” does not contain a Null value, the expression will display the result of the second, “nested” IIf function, which will be either “A” or “B”.

You can build quite complex and powerful expressions using nested IIf functions. They can, however, very quickly become difficult to read, and therefore difficult to modify when the need arises. You may find that you can achieve the same objective in a more easily maintained way using one of the other conditional functions, Choose or Switch. (I covered the Choose function in an earlier post, and I’ll cover the Switch function in a forthcoming post.)

There’s an interesting difference between the behaviour of the IIf function in VBA code and the behaviour of the same function in Jet queries. In VBA code, while the IIf function will return only one of the “truepart” or “falsepart” arguments, it will attempt to evaluate both of them. This can have “interesting” side effects. For example, suppose you attempt, in VBA code, to use the IIf function to avoid a potential divide-by-zero error:

Public Function TestIIf(TestInteger As Integer, TestLong As Long)

    TestIIf = IIf(TestInteger = 0, "N/A", TestLong / TestInteger)
    
End Function

This won’t work. If you call this function with a value of 0 in the TestInteger argument, you’ll still get a divide-by-zero error. This is because, even though the function is not going to return the result of the “falsepart” argument (TestLong / TestInteger) it still attempts to evaluate that expression, thereby raising the very error you were trying to avoid. (You can avoid this in VBA code by using If … Then … Else … End If instead of IIf).

In a Jet query, the “falsepart” argument is not evaluated if the “expr” argument (the condition to be tested) evaluates to True. The IIf function returns the value of the “truepart” argument and does not attempt to evaluate the “falsepart” argument in those circumstances. So the same expression can be used successfully to avoid the divide-by-zero error in a query:

SELECT IIf([TestInteger]=0,"N/A",[TestLong]/[TestInteger]) AS TestIIf FROM tblTest;

This works, successfully avoiding divide-by-zero errors, both within the Microsoft Access environment and when executed by our C# “JetTest” utility (see previous posts).


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.


Wednesday, May 05, 2004

 

Functions in Microsoft Jet Queries

Functions Beginning with “G”, “H” and “I”

A few posts back, I outlined a proposed schedule in which I would alternate between the list of VBA functions available in Microsoft Jet queries when executed outside the Microsoft Access environment and some of the other topics mentioned in previous posts. I’ve departed somewhat from that proposed schedule, partly because I wanted to get through the list of functions more quickly, partly because, as I mentioned in a previous post, I recently stopped smoking, and the withdrawal symptoms have left me with an attention span of which a goldfish would be ashamed!

This week, however, I’m at least temporarily back on track. This post will be all about the list of functions, but as one of the functions we’ll examine today is the IIf function, and as that is one of the conditional functions, the discussion of which was the original impetus for this whole series, I’ll take some time out in the next post to discuss the syntax and use of the IIf() function.

All of the VBA functions beginning with the letter “G” (GetAllSettings, GetAttr, GetObject and GetSetting) are blocked in “sandbox” mode. Here’s the SQL for a query that uses all of the functions with names beginning with the letter “H”:

SELECT 
    Hex([TestInteger]) AS TestHex, 
    Hex$([TestInteger]) AS TestHexString, 
    Hour(Now()) AS TestHour 
FROM tblTest;

This query ran without error both within the Microsoft Access environment, and also when executed via our C# “JetTest” utility (see earlier posts). So we can add these three functions to our list. If you look closely, you’ll notice that we could also add the Now function to the list. (But I won’t, yet, as that would only confuse me when I reach the letter “N”!)

Of the functions with names beginning with the letter “I”, the following functions are blocked in “sandbox” mode: Input, Input$, InputB, and InputB$.

The IMEStatus function is only meaningful in East Asian versions of Windows, and I don’t have a PC with an East Asian version of Windows installed on which to test it. It does not raise any error when used in a query either within the Microsoft Access environment or via the “JetTest” utility.

The InputBox, IRR, and IsArray functions all failed with “undefined function” error messages when used in a query executed within the Microsoft Access environment.

Here’s the SQL for a query that uses all of the remaining functions with names beginning with the letter “I”:

SELECT 
    IIf([TestBool],"A","B") AS TestIFF, 
    IMEStatus() AS TestIMEStatus, 
    InStr(1,[TestText],"e") AS TestInStr, 
    InStrB(1,[TestText],"e") AS TestInStrB, 
    InStrRev([TestText],"e") AS TestInStrRev, 
    Int([TestDouble]) AS TestInt, 
    IPmt([TestDouble],[TestDouble],[TestDouble],[TestDouble]) AS TestIPmt, 
    IsDate([TestText]) AS TestIsDate, 
    IsEmpty([TestText]) AS TestIsEmpty, 
    IsError([TestText]) AS TestIsError, 
    IsMissing([TestText]) AS TestIsMissing, 
    IsNull([TestText]) AS TestIsNull, 
    IsNumeric([TestText]) AS TestIsNumeric, 
    IsObject([TestText]) AS TestIsObject 
FROM tblTest;

Much to my surprise, the only one of these functions that failed when run through the “JetTest” utility was the InStrRev function. Here’s the final version of this query, which executed without error both within the Microsoft Access environment and also via the “JetTest” utility:

SELECT 
    IIf([TestBool],"A","B") AS TestIFF, 
    IMEStatus() AS TestIMEStatus, 
    InStr(1,[TestText],"e") AS TestInStr, 
    InStrB(1,[TestText],"e") AS TestInStrB, 
    Int([TestDouble]) AS TestInt, 
    IPmt([TestDouble],[TestDouble],[TestDouble],[TestDouble]) AS TestIPmt, 
    IsDate([TestText]) AS TestIsDate, 
    IsEmpty([TestText]) AS TestIsEmpty, 
    IsError([TestText]) AS TestIsError, 
    IsMissing([TestText]) AS TestIsMissing, 
    IsNull([TestText]) AS TestIsNull, 
    IsNumeric([TestText]) AS TestIsNumeric, 
    IsObject([TestText]) AS TestIsObject 
FROM tblTest;

As always in these tests, the names in square brackets (“TestBool”, “TestText”, “TestDouble” etcetera) are names of fields within the table “tblTest”, and their data types are as indicated by their names – “TestBool” is a Boolean (Yes/No) field, “TestText” is a text field, etcetera. The purpose of this query is merely to ascertain whether the functions will execute without error, not to return a meaningful result. In particular, I can’t think of a practical use for the IsEmpty, IsMissing or IsObject functions within a query, especially not a query that is executed outside of the Microsoft Access environment. With that caveat, the full list of functions that we can add to our list as a result of today’s tests is:

Hex, Hex$, Hour, IIf, IMEStatus, InStr, InStrB, Int, IPmt, IsDate, IsEmpty, IsError, IsMissing, IsNull, IsNumeric, and IsObject.


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.


Friday, April 30, 2004

 

Functions in Microsoft Jet Queries

Functions Beginning with “F”

According to the schedule I outlined a few posts back, I’m supposed to be writing about something other than the list of functions available in Microsoft Jet queries today. However, I’m still feeling a bit twitchy and not very creative or adventurous, as a result of giving up smoking, so I’m going to depart from schedule a little and continue the list with functions beginning with the letter “F”.

I did find (thanks, Mike!:)) a Microsoft Knowledge Base article (http://support.microsoft.com/default.aspx?kbid=294698) that includes a list of functions that can be used in Microsoft Jet queries. However, the results of my tests so far differ somewhat from that list, so it seems worthwhile to continue with the tests.

Of the functions beginning with “F”, the following functions are blocked in “sandbox” mode: FileAttr, FileDateTime, FileLen, and FreeFile.

The Filter function returns an array, and therefore I can’t think of a practical way of testing it in a query, except perhaps by combining it with another function that takes an array as one of its arguments. We haven’t come across such a function as yet. I’ll try to introduce tests for the Filter function (and the Array function, which we skipped over earlier for the same reason) if and when an opportunity arises.

Here’s a query that includes all of the remaining functions with names beginning with “F”:

SELECT Fix([TestDouble]) AS TestFix, Format([TestDate],"Long Date") AS TestFormatVar, Format$([TestDate],"Long Date") AS TestFormatString, FormatCurrency([TestDouble]) AS TestForCur, FormatDateTime([TestDate]) AS TestForDate, FormatNumber([TestDouble]) AS TestForNum, FormatPercent([TestDouble]) AS TestForPer, FV([TestSingle],[TestLong],[TestDouble]) AS TestFV FROM tblTest;

As always in these tests, “TestDouble”, “TestDate”, “TestSingle” and “TestLong” are fields in the table “tblTest”, and their data types, as the names suggest, are Double, Date/Time, Single, and Long Integer, respectively.

This query executed without error when run within Microsoft Access. When run through our JetTest utility, however, the FormatCurrency, FormatDateTime, FormatNumber and FormatPercent functions all failed with ‘undefined function’ error messages. Here’s the final form of the query that executed without error via JetTest:

SELECT Fix([TestDouble]) AS TestFix, Format([TestDate],"Long Date") AS TestFormatVar, Format$([TestDate],"Long Date") AS TestFormatString, FV([TestSingle],[TestLong],[TestDouble]) AS TestFV FROM tblTest;

The functions that we can add to our list of functions that can be used in Microsoft Jet queries executed outside of the Microsoft Access environment, then, are Fix, Format, Format$, and FV.


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.


Wednesday, April 28, 2004

 

Functions in Microsoft Jet Queries

Functions Beginning with "E"

These posts may be a little intermittent and shorter than usual for the next few days. Your humble scribe is currently in the throes of an attempt to give up the dreaded cigarettes, and as a result isn’t really fit for human company – not even electronic company! :( Normal service will resume as soon as possible.

We’ve now reached the letter “E” in our alphabetical investigation into precisely which functions can be used in Microsoft Jet queries outside the Microsoft Access environment. There aren’t many functions with names beginning with “E”, and of those that do exist, the Environ, Environ$, and EOF functions are among the functions that are blocked by the new “sandbox” mode in Microsoft Access 2003. That leaves just the Error, Error$, and Exp functions. Here’s a query that uses each of these three functions. As usual in these examples, “TestInteger” and “TestDouble” are fields in the table “tblTest”, and as the names suggests the data types of these fields are Integer and Double, respectively.

SELECT Error([TestInteger]) AS TestErrVar, Error$([TestInteger]) AS TestErrString, Exp([TestDouble]) AS TestExp FROM tblTest;

This query turned up the first two functions in the list that work in a query executed within the Microsoft Access environment, but failed when the same query was executed using our JetTest utility. Both the Error and Error$ functions worked as expected within the Microsoft Access environment, but failed with “undefined function” error messages when the same query was executed via the JetTest utility. The Exp function worked as expected in both environments. The final form of the query, which executed without error via JetTest, is simply …

SELECT Exp([TestDouble]) AS TestExp FROM tblTest;

So the only “E” function that we can add to our list is the Exp function.


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.


Monday, April 26, 2004

 

A C# Utility to Test Microsoft Jet Queries

In previous posts, we investigated some of the functions that can be used in Microsoft Jet queries, with particular emphasis on those that can be used in queries executed outside the Microsoft Access environment. For testing purposes, we developed a C# console application that I call “JetTest”.

The original version of JetTest was a fairly crude beast. Both the data source (the path and name of the database) and the name of the query to be executed were hard-coded. To run a test against a different query, or a different database, we had to modify and recompile the code. In the second version, we accepted the name of the query as a command-line parameter. This was an improvement, but still a bit clumsy. If we forgot to specify the query name when running the utility, we had to exit the program and start over. So (in addition to some general tidying up of the code) one of the first changes I wanted to make was to handle this a little more gracefully. The relevant code now looks like this:

if (args.Length == 0)
{
    System.String strPrompt = "Please enter the name " +
        "of the query you wish to test, then press " +
        "Enter";
    System.Console.WriteLine (strPrompt);
    strQueryName = System.Console.ReadLine().Trim();
}
else
    strQueryName = args[0].Trim();

if (strQueryName.Length == 0)
{
    System.Console.WriteLine("No query name specified.");
    return;
}

The program first checks the array “args”, which contains any command-line arguments that were passed to the program when it was executed. If the length of the array is zero, no arguments were passed. As in the first version, the program informs the user of the problem, but this time the user doesn’t have to exit the program and start again. The program will accept a query name from the user and continue. If the length of the string variable “strQueryName” is zero at this point, the user has still failed to enter a query name. The only sensible reason I can think of for this to happen is that the user executed the program by mistake, and does not want to proceed, so the program will simply exit if this happens.

We could handle the data source the same way, but the data source is likely to be a considerably longer string of text, and we probably won’t want to change it very often. In order to avoid any risk to important data, we really should set aside a database that will be used solely for testing purposes. We can always import data from other databases into the test database if we want to run tests against specific data. Entering a long path and name every time we run JetTest would be an unnecessarily inconvenient and error-prone process. Instead, I decided to use an XML configuration file.

The Microsoft .NET framework provides classes to support reading configuration data from an XML file called app.config (for Windows Forms and console applications) or Web.config (for ASP.NET applications). The app.config file for JetTest looks like this:

<?xml version="1.0"?>
<configuration>
    <appSettings>
        <!-- 
            Change the 'value' attribute ('C:\Documents and 
            Settings' etc.) to the path and name of your 
            test MDB. 
        -->
        <add key = "DataSource" value = "C:\Documents and Settings\Brendan Reynolds\My Documents\Blog\Test2.mdb">
        </add>
    </appSettings>
</configuration>

The relevant C# code to read the data source from the configuration file looks like this:

    // Edit the path and name in the configuration file
    // (app.config) to point to your test MDB. See the
    // additional comment in the configuration file.
    System.String strDataSource=
        System.Configuration.ConfigurationSettings.
        AppSettings["DataSource"];

You still need to change the path and name in the configuration file (an XML file is just a text file that includes tags that have special meaning to an XML parser, you can edit it in any text editor, such as Notepad) but you only have to do it occasionally (when the location or name of your test database changes) and you don’t have to recompile the code.

I also tidied up the display a bit by separating the colums with tabs instead of commas. A query with a small number of not-too-wide columns now displays quite neatly. A query with a large number of columns or with very wide columns is never going to look good in a console application. For that you really need features that aren’t available in a console application, like grids and scrollbars.

Here’s the complete source code, with some additional comments.

namespace BJR.JetTest
{
    /// 
    /// JetTest is a console application intended to test the use of 
    /// functions in Microsoft Jet queries.
    /// 
    class JetTest
    {
        /// 
        /// The main entry point for the application. Accepts a 
        /// single argument, the name of the saved Microsoft Jet 
        /// query to be tested.
        /// 
        [System.STAThread]
        static void Main(string[] args)
        {
            System.String strQueryName = System.String.Empty;

            // Did the user specify a query name?
            if (args.Length == 0)
            {
                System.String strPrompt = "Please enter the name " +
                    "of the query you wish to test, then press " +
                    "Enter";
                System.Console.WriteLine (strPrompt);
                strQueryName = System.Console.ReadLine().Trim();
            }
            else
                strQueryName = args[0].Trim();
            if (strQueryName.Length == 0)
            {
                System.Console.WriteLine("No query name specified.");
                return;
            }

            // Connect to the database.
            System.Data.OleDb.OleDbConnection odcn = 
                new System.Data.OleDb.OleDbConnection();
            try
            {
                System.String strProvider = 
                    "Microsoft.Jet.OLEDB.4.0";

                // Edit the path and name in the configuration file
                // (app.config) to point to your test MDB. See the
                // additional comment in the configuration file.
                System.String strDataSource = 
                    System.Configuration.ConfigurationSettings.
                    AppSettings["DataSource"];

                // Concatenate the provider and data source to build 
                // the connection string.
                System.String strConnectionString = 
                    "Provider = " + strProvider + "; " + 
                        "Data Source = " + strDataSource;
                odcn.ConnectionString = strConnectionString;
                odcn.Open();
                System.Data.OleDb.OleDbCommand odcm = 
                    new System.Data.OleDb.OleDbCommand();
                odcm.CommandText = strQueryName;
                odcm.CommandType = 
                    System.Data.CommandType.StoredProcedure;
                odcm.Connection = odcn;

                // Retrieve the data.
                System.Data.OleDb.OleDbDataReader oddr = 
                    odcm.ExecuteReader(System.Data.CommandBehavior.
                    CloseConnection);

                // Loop through the records.
                while (oddr.Read())
                {

                    // Loop through the columns.
                    for (System.Int32 intCol = 0; 
                        intCol < oddr.FieldCount; intCol++)
                    {

                        // Display the result.
                        System.Console.Write(oddr.GetValue(intCol).
                            ToString());
                        if (intCol < (oddr.FieldCount - 1))
                        {
                            System.Console.Write("\t");
                        }
                        else
                        {
                            System.Console.WriteLine();
                        }
                    }
                }
                oddr.Close();
                System.Console.Read();
            }
            catch (System.Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                System.Console.Read();
            }
            finally
            {
                odcn.Dispose();
            }
        }
    }
}


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.


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.

SELECT 
    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.


Thursday, April 22, 2004

 

Conditional Expressions in JET SQL

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

Part Four

As the title of this series indicates, the original intention of the series was to explain the use of conditional expressions in JET SQL. We’ve strayed somewhat from that original intention, largely because some of the issues raised appear to be undocumented (or if they are documented, said documentation seems to be remarkably well hidden) necessitating a lot more trial-and-error investigation than I’d originally expected.

In today’s post, I’m going to return to the original subject, and examine one of the conditional functions that can be used in JET queries, both within and outside of the Microsoft Access environment, namely the Choose function.

The Choose function takes a variable number of arguments. The first argument (referred to as “index” in the help files) is a numeric expression. The subsequent arguments (referred to as “choice-1”, “choice-2”, etcetera in the help files) form a list of possible values that the function may return. The value returned is the value of the argument in the position within the list that matches the value of the “index” argument. In other words, if the “index” argument evaluates to the number 1, the function will return the value of the first argument in the list following the “index” argument. If the “index” argument evaluates to the number 2, the value of the second argument in the “choice” list is returned, etcetera.

If the value of the “index” argument is not a whole number, it is rounded to the nearest whole number before being evaluated.

Clear as mud, right? Perhaps an example might help.

The “Northwind” sample database that comes with Microsoft Access includes a table, “Shippers”, with an AutoNumber primary key field (“ShipperID”) and a text field, “CompanyName”, that stores the names of shipping companies. It contains just three records, with the following values:

ShipperIDCompanyName
1Speedy Express
2United Package
3Federal Shipping

The “Orders” table includes a foreign key field, “ShipVia” that contains values from the “ShipperID” field in the “Shippers” table, i.e. the “ShipperID” of the shipping company that shipped that order.

If you look at the “Orders” table in datasheet view in Microsoft Access, you’ll see the name of the shipping company rather than the “ShipperID”, because the field is defined as a “lookup field”. But this is an Access-specific feature and even within Microsoft Access it controls only what is displayed on the screen. What is actually stored in the “Orders” table is the numeric value or the ShipperID field, not the textual company name that you see displayed. If we were to define a query such as “SELECT ShipVia FROM Orders” and display the results of that query outside of Microsoft Access, in a Microsoft Excel worksheet, a Microsoft Word document, or our very own “JetTest” utility, we’d get the numeric “ShipperID” values, not the company names.

One way to get the shipping company names along with the order details is to join the two tables in a query such as the following …

SELECT Orders.OrderID, Orders.OrderDate, Shippers.CompanyName AS Shipper FROM Shippers INNER JOIN Orders ON Shippers.ShipperID = Orders.ShipVia;

In most circumstances, this is likely to be the best and most efficient way to retrieve this information. But as an example of how to use the Choose function, here’s how we can retrieve the same information using the Choose function and without the join …

SELECT OrderID, OrderDate, Choose([ShipVia], "Speedy Express", "United Package", "Federal Shipping") AS Shipper FROM Orders;

If the “ShipVia” field contains the value 1, the Choose function will return the first item in the list of choices, “Speedy Express”. If the “ShipVia” field contains the value 2, the Choose function will return the second item in the list of choices, “United Package”. And if the “ShipVia” field contains the value 3, the Choose function will return the third item in the list of choices, “Federal Shipping”.

If the value of the “index” argument (the “ShipVia” field in the example above) is less than 1 or greater than the number of items in the list of choices, the Choice function returns Null (a special value indicating a missing or unknown value, which displays as an empty field in the Microsoft Access datasheet view). So in our example, if the “ShipVia” field contained a number less than 1 or a number greater than 3, the “Shipper” column in the query would contain Null. If the “ShipVia” field itself contained a Null value, that would cause an error. The “index” argument must evaluate to a numeric value, and Null is not a number. At the expense of introducing a “forward reference” (something that won’t be explained until later) here’s how we could avoid such errors, using the IIf() function …

SELECT OrderID, OrderDate, ShipVia, IIf([ShipVia] Is Null, "N/A", Choose([ShipVia], "Speedy Express", "United Package", "Federal Shipping")) AS Shipper FROM Orders;

While the Choose function returns the value of only one of the “choice” arguments, it evaluates all of them. This is of little consequence when the arguments are simple string literals as in our example, but can have “side effects” when the arguments are more complex expressions, perhaps including calls to other functions. Take the following example, where the “choice” arguments are calls to a custom VBA function ("MyMsgBox") that in turn calls the built-in MsgBox method …

The query …

SELECT OrderID, OrderDate, ShipVia, IIf([ShipVia] Is Null, "N/A", Choose([ShipVia], MyMsgBox("Speedy Express"), MyMsgBox("United Package"), MyMsgBox("Federal Shipping"))) AS Shipper FROM Orders WHERE OrderID = 10248;

The VBA function …

Public Function MyMsgBox(ByVal TheMessage As String) As String

    MsgBox TheMessage
    MyMsgBox = TheMessage

End Function

While this will return the correct value in the query, it will display three message boxes, one for each of the three items in the list of choices. That’s why I added the criteria (“WHERE OrderID = 10248”) to limit the result to just one record – I didn’t feel like clicking through three message boxes for every record in the Orders table!

If you came here looking for information about using conditional expressions in JET SQL, I hope this post goes some way toward making up for the diversions from that subject in previous posts. If, on the other hand, you were actually more interested in some of the other subjects, don’t worry – I’ll be returning to those in subsequent posts.

Future plans include (as always, in no particular order and in no specific time-frame) …

Continue the investigation into which functions are available in JET SQL when used outside the Microsoft Access environment. Explain the syntax and use of the other conditional functions, IIf and Switch. Enhance the JetTest test utility and perhaps produce VB.NET and/or VBA versions. Explain how to build the utility for those who may not be familiar with VS.NET.


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.


Wednesday, April 21, 2004

 

Conditional Expressions in JET SQL

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

Part Three

Previous posts in this series raised the questions “just what functions can be used in JET queries outside of the Microsoft Access environment?” and “what exactly is the relationship between the JET database engine and the VBA expression service?”

I’m still looking for documentation to answer the first question. (The MDAC SDK lead turned out to be a dead end.) In the meantime, I’ll continue with the trial-and-error process I started in Part Two.

The answer to the second question turns out to be, in general terms at least, fairly simple and not terribly exciting. The VBA expression service is a DLL (Dynamic Link Library). Dynamic link libraries are designed to be shared by multiple applications. So just because the VBA expression service happens to have the letters “VBA” in its name doesn’t mean that it can only be used by VBA, or by an application that hosts VBA, such as Microsoft Access. In general (and anthropomorphic) terms, the JET database engine “knows” how to call functions in the DLL.

In Part Two of this series, we started working our way through the alphabetical list of functions in the VBA help file by testing functions with names that begin with the letter “A”. There aren’t any functions in the list with names beginning with the letter “B”, so the next group of functions to look at are those with names that begin with the letter “C”. It’s a big group, because it includes all the data-type conversion functions which, not surprisingly, begin with “C”, presumably for “Convert”. This group also includes the “Choose” function, one of the conditional functions that this series of posts was supposed to be about in the first place, which is just as well, because I’d almost forgotten about that. (Hey, this is a blog, it’s supposed to be spontaneous, OK?)

As with the Array function that we skipped over in Part Two, some of these functions just wouldn’t make much sense in a query, at least not without being combined in complex expressions with other functions, so we’ll leave them aside for now. Here’s a SQL statement for a query that includes all of the “C” functions that seem relevant and testable in a query …

SELECT
    CBool([TestLong]) AS TestCBool, 
    CByte([TestDouble]) AS TestCByte, 
    CCur([TestDouble]) AS TestCCur, 
    CDate([TestDouble]) AS TestCDate, 
    CDbl([TestDate]) AS TestCDbl, 
    Choose([TestDouble], "One", "Two", "Three", "Four", "Five") AS TestChoose, 
    Chr([TestLong]+34) AS TestChr, 
    Chr$([TestLong]+34) AS [TestChr$], 
    ChrW([TestLong]+34) AS TestChrW, 
    CInt([TestDouble]) AS TestCInt, 
    CLng([TestDouble]) AS TestCLng, 
    Cos([TestDouble]) AS TestCos, 
    CSng([TestDouble]) AS TestCSng, 
    CStr([TestDouble]) AS TestCStr, 
    CVar([TestDouble]) AS TestCVar, 
    CVDate([TestDouble]) AS TestCVDate
FROM tblTest
WITH OWNERACCESS OPTION;

As the names imply, "TestLong" is a long integer field, "TestDouble" is a double field, and "TestDate" is a date/time field.

Again, please remember that the purpose of this rather strange query is not to return meaningful results, but simply to determine whether these functions can be used in JET queries when executed outside the Microsoft Access environment.

The functions that I didn’t attempt to test were “CallByName”, “Command”, “Command$”, and “CreateObject”.

The following functions failed with “undefined function” error messages when I attempted to display the result of the query in datasheet view within Microsoft Access: “ChrB”, “ChrB$”, “CurDir”, “CurDir$”, and “CVErr”. I was expecting the “CurDir” and “CurDir$” functions to be blocked by the new JET “sandbox” mode introduced with Access 2003, but I was expecting a different error message. However, that’s an investigation for another day.

The behaviour of the “CDec” function was also unexpected. When I attempted to use it in the query, in the form “CDec([TestDouble])” I received the following error message: “The expression you entered has a function containing the wrong number of arguments.”

The “CDec” function, when used in VBA code, takes one argument. But a little experimentation soon showed that when used in the query, the function expected two arguments. Given two numeric arguments, the function executed without error in the query, but despite experimenting with various argument values, it always returned the value 0. This, too, will have to be an investigation for another day.

I saved this query using the name “qryTestFunctionsC”.

The next step was to modify the C# test code a little, to accept the name of the query to run as a command-line argument, instead of having to edit the code to change the hard-coded query name for every test …

using System;

namespace JetTest
{
    /// 
    /// Summary description for Class1.
    /// 
    Class Class1
    {
        /// 
        /// The main entry point for the application.
        /// 
        [STAThread]
        static void Main(string[] args)
        {
            if (args.Length == 0)
            {
                Console.WriteLine (@"No query specified.");
                Console.WriteLine();
                Console.WriteLine(@"TestJet requires the name of the query you");
                Console.WriteLine(@"wish to run as a command-line argument.");
                Console.WriteLine();
                Console.WriteLine(@"Example: TestJet myquery where 'myquery' is");
                Console.WriteLine(@"the name of the query you wish to run).");
                Console.WriteLine();
                Console.WriteLine(@"Please press Enter to exit, then try again.");
                Console.Read();
                return;
            }
            System.Data.OleDb.OleDbConnection TheConnection =
                new System.Data.OleDb.OleDbConnection();
            try
            {
                System.String strProvider = @"Microsoft.Jet.OLEDB.4.0";

                // Change this to the actual location of your test MDB.
                System.String strDataSource =
                    @"C:\Documents and Settings\Brendan Reynolds\My Documents\Blog\Test2.mdb";

                System.String strConnectionString =
                    @"Provider = " + strProvider + @"; " + @"Data Source = " + strDataSource;
                TheConnection.ConnectionString = strConnectionString;
                TheConnection.Open();
                System.Data.OleDb.OleDbCommand TheCommand = new System.Data.OleDb.OleDbCommand();
                TheCommand.CommandText = args[0];
                TheCommand.CommandType = System.Data.CommandType.StoredProcedure;
                TheCommand.Connection = TheConnection;
                System.Data.OleDb.OleDbDataReader TheReader =
                    TheCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                While (TheReader.Read())
                {
                    for (System.Int32 i = 0; i < TheReader.FieldCount; i++)
                    {
                        Console.Write(TheReader.GetValue(i).ToString());
                        if (i < (TheReader.FieldCount - 1))
                        {
                            Console.Write(@", ");
                        }
                        Else
                        {
                            Console.WriteLine();
                        }
                    }
                }
                TheReader.Close();
                Console.Read();
            }
            catch (System.Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                System.Console.Read();
            }
            finally
            {
                TheConnection.Dispose();
            }
        }
    }
}

There’s a lot more we could do to make this code more generic and reusable, for example by accepting the path to the MDB as an argument, and accepting an arbitrary SQL statement rather than requiring the name of a saved query. But aside from the fact that all those parameters would make the application too cumbersome as a command-line driven console application, forcing me to change it to a Windows Forms application, there are security implications to consider here. I don’t want to be held responsible if you accidentally run a DELETE query against your production data. As the application stands at the moment, as long as the hard-coded path points to an MDB that you use solely for testing purposes, the application will not have any access (no pun intended) to production databases. And as long as you don’t save any action queries in your test database, the application will not be able to modify any data even in the test database.

I may very well add additional functionality to the test code in the future, but I’m not going to rush into it without adding safeguards, such as checking for keywords like “DELETE”, “INSERT”, “UPDATE”, etcetera in SQL statements.

We can now run the test application (I called it JetTest) from the command prompt and pass the name of the query as a parameter, e.g. “JetTest qryTestFunctionsC”. The code executes the new query without error. The output is pretty ugly, but that’s not the point. Other than a few formatting differences, it’s the same output that was produced by running the query in the Microsoft Access user interface. So we can now add the following functions to our list of functions that can be used in JET queries executed outside of the Microsoft Access environment:

CBool, CByte, CCur, CDate, CDbl, Choose, Chr, Chr$, ChrW, CInt, CLng, Cos, CSng, CStr, CVar, and CVDate.

Future plans for this series include, in no particular order, and in no specific time-frame …

Test more JET functions. Explain the syntax and use of the Choose function. Enhance the JetTest test utility and perhaps produce VB.NET and/or VBA versions. Explain how to build the test app for those who may not be familiar with VS.NET.


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.


Tuesday, April 20, 2004

 

Conditional Expressions in JET SQL

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

Part Two

In Part One of this series, I wrote that in future articles I would try to “delve a little deeper into the question of just what functions are supported directly by the JET database engine”. As I began to research this question, I soon discovered two things …

The first thing that I discovered was that I’d taken on a much more difficult task than I’d anticipated, as I’ve yet to find any documentation describing which functions can and can not be used in JET queries when executed outside of the Microsoft Access environment. There appears to be nothing about this in the Microsoft Access or VBA help files, or in the MSDN (Microsoft Developer Network – http://msdn.microsoft.com) library. I found a few references that lead me to believe that there may be something relevant in the MDAC SDK, but haven’t had an opportunity to follow up on those yet – watch this space.

The second thing that I discovered was that some of what I wrote in Part One of this series may not be entirely accurate, or at least, requires some further clarification. The following is a quote from the Microsoft Access 2003/VBA help topic “SQL Expressions”, which you’ll find under “Microsoft Jet SQL Reference” in the help table of contents …

“The Microsoft Jet database engine uses the Microsoft Visual Basic for Applications (or VBA) expression service to perform simple arithmetic and function evaluation. All of the operators used in Microsoft Jet 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 you can use in SQL expressions. For example, you can use these VBA functions to compose an SQL query in the Microsoft Access query design view, and you can also use these functions in an SQL query in the DAO OpenRecordset method in Microsoft Visual C++, Microsoft Visual Basic, and Microsoft Excel code.”

In light of this information, it’s clearly not entirely accurate to talk about “functions supported directly by the Microsoft JET database engine”. It would appear that, in addition to trying to establish just which functions can and can not be used in JET SQL statements executed outside the Microsoft Access environment, I now also need to attempt to clarify just what the relationship is between the JET database engine and the VBA expression service. Watch this space again …

In the meantime, I’ll resort to some good old-fashioned trial-and-error. The Microsoft Access 2003/VBA help files include an alphabetical list of VBA functions. There are six functions with names beginning with the letter “A”, namely Abs, Array, Asc, AscB, AscW, and Atn. I can’t think of a means (let alone a reason) to use the Array function in a query, at least not without combining it with other functions, so I’ll ignore that one for now. Here’s the SQL for a query that includes each of the other five functions …

SELECT 
    Abs([TestLong]) AS TestAbs, 
    Asc([TestText]) AS TestAsc, 
    AscB([TestText]) AS TestAscB, 
    AscW([TestText]) AS TestAscW, 
    Atn([TestLong]) AS TestAtn
FROM tblTest
WITH OWNERACCESS OPTION;

As the names imply, “TestLong” is a long integer field, and “TestText” is a text field.

This query is not intended to return a meaningful result. The point here is not to demonstrate the use of these particular functions, but simply to investigate whether they can be used in queries executed outside the Microsoft Access environment.

Having saved this query (I called it “qryTestFunctionsA”) I next needed some non-Access test code to exercise it. I used C#, simply because that’s what I happen to be working with these days when I’m not working with Access. If you prefer, you can adapt the code from Part One of this series and use it in Microsoft Excel, Microsoft Visual Basic, or Microsoft Word …

using System;

namespace TestFunctionsA
{
    /// 
    /// Summary description for Class1.
    /// 
    Class Class1
    {
        /// 
        /// The main entry point for the application.
        /// 
        [STAThread]
        static void Main(string[] args)
        {
            System.Data.OleDb.OleDbConnection TheConnection =
                new System.Data.OleDb.OleDbConnection();
            try
            {
                System.String strProvider = @"Microsoft.Jet.OLEDB.4.0";

                // Change this to the actual location of your test MDB.
                System.String strDataSource =
                    @"C:\Documents and Settings\Brendan Reynolds\My Documents\Blog\Test2.mdb";

                System.String strConnectionString =
                    @"Provider = " + strProvider + @"; " + @"Data Source = " + strDataSource;
                TheConnection.ConnectionString = strConnectionString;
                TheConnection.Open();
                System.Data.OleDb.OleDbCommand TheCommand = new System.Data.OleDb.OleDbCommand();
                TheCommand.CommandText = @"qryTestFunctionsA";
                TheCommand.CommandType = System.Data.CommandType.StoredProcedure;
                TheCommand.Connection = TheConnection;
                System.Data.OleDb.OleDbDataReader TheReader =
                    TheCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                While (TheReader.Read())
                {
                    for (System.Int32 i = 0; i < TheReader.FieldCount; i++)
                    {
                        Console.Write(TheReader.GetValue(i).ToString());
                        if (i < (TheReader.FieldCount - 1))
                        {
                            Console.Write(@", ");
                        }
                        Else
                        {
                            Console.WriteLine();
                        }
                    }
                }
                TheReader.Close();
                Console.Read();
            }
            catch (System.Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                System.Console.Read();
            }
            finally
            {
                TheConnection.Dispose();
            }
        }
    }
}

The code runs without error, and (given my test data) produces the following output …

1, 111, 111, 111, 0.785398163397448
2, 116, 116, 116, 1.10714871779409
3, 116, 116, 116, 1.24904577239825
4, 102, 102, 102, 1.32581766366803
5, 102, 102, 102, 1.37340076694502

The conclusion? We can safely say that the Abs, Asc, AscB, AscW and Atn functions can all be used in Microsoft JET queries outside the Microsoft Access environment.

Future plans for this series include testing more VBA functions, modifying the C# test code to be more generic and reusable, and the continuing hunt for documentation on these issues.


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.


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.


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