Running SQL through VBA

Question

I am trying to run the following query:

SELECT Project.* FROM Project WHERE (((Projects.[End Date (PPL)])>=DateAdd('m',-6,Date())));

It runs fine in Access (2007) however when i move it over to VBA:

strQry = "SELECT Project.* FROM Project WHERE (((Projects.[End Date (PPL)])>=DateAdd('m',-6,Date())));"

I get an error message saying 'No value given for one or more required parameters'

I can get it running with the following code but it doesn't provide the correct information:

strQry = "SELECT Project.* FROM Project WHERE Project.[End Date (PPL)] >= DateAdd('m',-6,Date());"

Any help would be great.

Answer 1

The definitive answer to your question is here, in StackOverflow:

VBA function in Excel ADODB query

The short answer is:

You're using a dialect of SQL called Jet-SQL, and most people call it from Microsoft Access, a database application which makes VBA functions available to the SQL engine.

If you run Jet SQL from any other platform - like an ODBC or ADODB wrapper -the VBA functions aren't available: all you have is the Jet-SQL native functions.

There's a list of the native Jet-SQL functions here: it's almost complete, and you can see that it's rather short:

MS Access: Functions - Listed by Category

...And it doesn't include NZ()

It's somewhat confusing that these functions look like the familiar VBA - and annoying that the web page listing them is labelled 'MS-Access' rather than Jet SQL.

There is an interesting item in that list: the inline 'If' function, IIF(), and you can use it as a substitute for NZ:

SELECT
    IIF(
        table1.[RIC] IS NOT NULL, 
        table1.[RIC],
        table2.[ISIN] + ', CODE:ISIN'
        )                               AS [RetrievalCode],
    table3.[Value], 
    table3.[DateStamp] 
FROM
    * The rest of your Query  *

Hope that helps: I suspect that it won't, because you may be calling a named query (or SQL with a named subquery in it), and all the non-native function calls in those queries are invalid unless the whole thing is called from within MS-Access.

You can, of course, make a 'schema' query and extract the SQL, parse it out, and replace all the VBA with native Jet-SQL functions. But the syntax for Jet-SQL gets arcane when you're outside it's intended environment - the Query-By-Example visual query builder inside Microsoft Access - so that suggestion may be more time and trouble than doing it some other way.