Jet SQL functions

[Stephen Howe]

Hi

I have Microsoft Access 2000 on this computer but I dont find that the help
describes Jet SQL functions.

Some searching on the Internet reveals Jet SQL functions but these are the
usual Group Aggregate functions : AVG, MAX, MIN, SUM, COUNT etc.
What I am interested in SQL Server equivalent functions to FLOOR, CEILING,
ROUND, CAST, CONVERT

Yet when I look in "Microsoft Access Help", there is nothing. It is
excpetionally poor at describing Jet SQL, practically zero.

Does anyone have a URL on Jet Maths functions?
And why is Microsoft Access so badly documented?

Thanks

Stephen Howe

 

[Conan Kelly]

Stephen,

I've found that it is really trick to get to the functions help.

Try this (this is for AC 2002/2003...hopefully 2000 will be similar):

-Open new query in desing view
-Right-click one of the blank "Field:" cells
-Click "Build..."
-in the left listbox of the 3 lower listbox, expand Functions
-Click "Built-in functions"
-Narrow your results by selecting a category in the middle listbox ("All"
should be selected by default)
-Double-click the desired function in the right list box
-Select the function name in the Expresion Builder text box above
-Click the help button

That should open up the Help to that function. From there, you should be
able to switch to the Contents tab in help and hopefully there will be a
list of all functions.

I hope these instructions work for you in AC 2000,

Conan

 

[Michel Walsh]

You generally call VBA functions. For CAST and CONVERT, you probably use
something like CDbl(x ) to convert x to a double, or CDec( x) to convert
x to a decimal. For a positive number, x, int(x) is the floor of x
and -int(-x) is the ceiling of x. Jet-SQL predefines a lot of automatic
data conversion, so, it is very really un-usual to 'convert' the data. Note
that for concatenation, + propagates the null while & does not, so you can
write:

LastName & ( ", " + FirstName )

which will return things like:

Smith
Smith, Jones

ie, in the first case, FirstName was null, the + propagates the null, and
so, the coma disappeared.


Instead of COALESCE(a, b), use NZ(a, b) , or uses iif( a IS NULL, b, a).

Instead of CASE WHEN c1 THEN v1 WHEN c2 THEN v2 ELSE v3 END
uses SWITCH( c1, v1, c2, v2, true, v3)


You can define your own VBA function, public, in a standard module, and use
them in Jet-SQL without having to do anything else. As usual, doing so,
there is no possibility for SQL to use an index over such a computed
expression, as it is the case for any computed expression.



Be careful if you use an outer join and there is only one table in the ON
clause. Jet pushes that condition in the WHERE clause, which leads to a
totally different behavior than in MS SQL Server:


SELECT a.*
FROM authors AS a LEFT JOIN books AS b
ON a.authorID=b.authorID AND a.city = 'Iqaluit'



will return nothing in Jet, but all the table authors in MS SQL Server, even
records where city <> 'Iqaluit'.




Hoping it may help,
Vanderghast, Access MVP

 

[david]

Jet SQL uses VBA math functions (and most other VBA functions)

Use VBA or VB help to get information on Jet SQL math functions.

The math functions are provided by hooks from Jet into the VB/VBA
library.

In Access 2000, to get information on the VBA functions like cLng()
Int(), press [ctrl][g] to open a VBA window, type in cLng, and get
help on that.

JET SQL was removed from the Access help, "because Access
can use other data sources". Math functions were removed from
Access help because VBA help was seperated from Access
help.

Access 2000 help was so bad that I still get disgusted thinking about it.

Help gradually got better again after Access 2000. Many developers
continued to use Access 97 help instead of Access 2000 help, but I
don't know that there would be anyone using Access 97 help instead
of Access 2007 help.

(david)