The Blog With No Name

Of the VBA functions with names beginning with the letter 'S', there are two which are blocked in 'sandbox' mode, and two which raised an 'undefined function' error when used in a query within the Microsoft Access environment.

The two functions that are blocked in 'sandbox' mode are 'seek' and 'shell'. The two that failed with 'undefined function' errors are 'Spc' and 'Split'. It is no surprise that these two failed, as the Spc function is intended for use with the VBA 'Print' statement, and the Split function returns an array.

The remaining functions with names that begin with the letter 'S' and executed without error in a query executed within the Microsoft Access environment are: Second, Sgn, Sin, SLN, Space, Sqr, Str, Str$, StrComp, StrConv, StrReverse, String, String$, Switch, and SYD.

Here's the SQL for the query I used to test these functions within the Microsoft Access environment:

SELECT 
    Second([TestDate]) AS TestSecond, 
    Sgn([TestInteger]) AS TestSgn, 
    Sin([TestDouble]) AS TestSin, 
    SLN([TestDouble],[TestDouble]/10,[TestLong]) AS TestSLN, 
    Space([TestLong]) & "|" AS TestSpace, 
    Sqr(Abs([TestDouble])) AS TestSqr, 
    Str([TestDouble]) AS TestStr, 
    Str$([TestDouble]) AS TestStrS, 
    StrComp([TestText],"m") AS TestStrComp, 
    StrConv([TestText],1) AS TestStrConv, 
    StrReverse([TestText]) AS TestStrReverse, 
    String([TestLong],[TestText]) AS TestString, 
    String$([TestLong],[TestText]) AS TestStringS, 
    Switch([TestInteger]>0,"Positive",
        [TestInteger]<0,"Negative",
        [TestInteger]=0,"Zero",
        [TestInteger] Is Null,"Null") AS TestSwitch, 
    SYD([TestDouble],[TestDouble]/10,[TestLong],[TestLong]\2) AS TestSYD
FROM tblTest
WITH OWNERACCESS OPTION;

Just one function, the StrReverse function, failed when the query was executed outside of the Microsoft Access environment, via our C# JetTest utility (see earlier posts). The removal of that column enabled the query to execute without error via JetTest also. So we can now add the following functions to our list of VBA functions that can be used in Jet queries executed outside of the Microsoft Access environment:

Second, Sgn, Sin, SLN, Space, Sqr, Str, Str$, StrComp, StrConv, String, String$, Switch, and SYD.

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 @ 2:25 PM