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.

# posted by Brendan Reynolds @ 11:13 AM