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.


Comments: Post a Comment

<< Home

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