Friday, September 17, 2004

 

Functions With Names Beginning With S

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.


Tuesday, September 07, 2004

 

Functions With Names Beginning With Q and R

There is only one VBA function with a name beginning with the letter ‘Q’, the QBColor() function. Most of us will probably never have occasion to use this function in a query, but if the occasion ever arises, you can rest assured that it works! :-)

The list of VBA functions with names beginning with the letter ‘R’ is a lot longer: Rate, Replace, RGB, Right, Right$, RightB, RightB$, Rnd, Round, RTrim, and RTrim$.

All of these functions executed without error in a query executed within the Microsoft Access environment. Here’s the SQL for the query that worked with Microsoft Access:

SELECT
    QBColor([TestByte]) AS TestQBColor, 
    Rate([TestDouble1],-[TestDouble2],[TestDouble3]) AS TestRate, 
    Replace([TestText],"e","x") AS TestReplace, 
    RGB([TestByte],[TestInteger],[TestLong]) AS TestRGB, 
    Right([TestText],3) AS TestRight, 
    Right$([TestText],3) AS TestRightS, 
    RightB([TestText],3) AS TestRightB, 
    RightB$([TestText],3) AS TestRightBS, 
    Rnd([TestByte]) AS TestRnd, 
    Round([TestDouble],2) AS TestRound, 
    RTrim([TestText]) AS TestRTrim, 
    RTrim$([TestText]) AS TestRTrimS
FROM tblTestWITH OWNERACCESS OPTION;

Just one function, the Replace 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:

QBColor, Rate, RGB, Right, Right$, RightB, RightB$, Rnd, Round, RTrim, and RTrim$.


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.


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