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.


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