What process executes the SQL statement?

Hello all,

What process executes the SQL statement? More importantly, how do I know
what functions I can use in SQL statements of MS Query?

If I connect to an Access DB, what process is executing the query? ...MS
Jet DB Engine? ...MS Query? ...XL?

When I created an external DB query to AC from XL, I tried to use the Date()
function in the SQL statement (ie.
Date(Year(DateFieldName),Month(DateFieldName),1) as 'FirstDayOfMonth').
When I did that, I got an error for something like "incorrect number of
arguments". So I tried DateSerial() and it worked.

How do I know what functions I can use in SQL statements of MS Query?
....functions of the DB system being queried (Access in this case)? ...XL
functions? ...VBA functions? ...MS Query functions?

Thanks for any help anyone can provide,

Conan Kelly

This is not an authoratative answer, but from observation:

The functions available in MSQuery when querying Access databases are
(a) standard SQL functions for aggregation (SUM, COUNT, MAX, MIN etc)
and (b) some standard VBA functions - as available in the host
application (for example, Left, AscB, but not Error)

I suspect there must be some interface into the Jet engine that allows
the calling application to provide functions that can be used.

For example, NZ is available in queries from Access but not from Excel.

I have never found a clear answer and have had to use trial-and-error.

Bill Manville
MVP - Microsoft Excel, Oxford, England