Query Function Reference Book

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a reference book that defines all of the query functions in Access?
I'm looking for something that describes what each function does and how to
use them. Thank you.
 
You mean like:

"C:\Program Files\Common Files\Microsoft Shared\OFFICE11\1033\JETSQL40.CHM"
Entry: Aggregate functions

The above is the location of the "Jet SQL Help" file for Access 2003 on my
machine, so your mileage may vary. I would think a local search for "JETSQL"
should turn up some likely candidates *if help files were installed*.

HTH,
 
I was really hoping to find a reference book. The Microsoft help is usually
pretty confusing. I have the Microsoft Press book "Microsoft Excel 97 -
Worksheet Function Reference" book and find it very helpful. I wnated to
find something similar for the Access query functions.
 
There are only a handful of "Access query functions". Not enough to support
a book, IMO.

Good Luck
 
There are only a handful of "Access query functions". Not enough to support
a book, IMO.
Click to expand...

I think you are referring to the set functions (MIN, MAX, AVG, etc)
whereas I think the OP is referring to expressions (IIF(), SWITCH(),
DATEPART(), etc).

Truth is, these have been omitted from the Help since the Jet 3.n days
(and even that archived content seems to have recently disappeared).
Broadly, it is the VBA5 scalar functions (as distinct from Methods)
that can be used in Access/Jet SQL; if you are using the Access user
interface you can add the scalar functions from both VBA6 and Access
functions to the list. It's essentially a trial and error basis and it
is the VBE Object Browser which is most helpful in such a trial. Note
than some behave differently in SQL (e.g. IIF(): in VBA both true and
false clauses are evaluated which is not the case when used in SQL),
then there the ones that have always been undocumented...

Jamie.

--
 
I think the OP is referring to expressions (IIF(), SWITCH(),
DATEPART(), etc).

Truth is, these have been omitted from the Help since the Jet 3.n days
(and even that archived content seems to have recently disappeared).
Broadly, it is the VBA5 scalar functions (as distinct from Methods)
that can be used in Access/Jet SQL; if you are using the Access user
interface you can add the scalar functions from both VBA6 and Access
functions to the list. It's essentially a trial and error basis and it
is the VBE Object Browser which is most helpful in such a trial. Note
than some behave differently in SQL (e.g. IIF(): in VBA both true and
false clauses are evaluated which is not the case when used in SQL),
then there the ones that have always been undocumented...
Click to expand...

This article may be of some use, notably the list of "Visual Basic for
Applications functions will cause an error when the functions are
called from an expression in a Jet query".

While the list of functions you can use in Jet queries when Sandbox
mode is enabled looks to be broadly the same as the expressions you
can use in Access/Jet queries without the Access user interface, there
are some that will not apply because their arguments and/or return
values require arrays e.g. NPV, array (!!!), etc.

Jamie.

--
 
Back
Top