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: