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):
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.