I use Excel Macro to run some Access query. NZ function works fine in Access.
But it doesn't work when I was connecting to Access through Excel macro. The
error message says it doesn't recognize the "NZ" function.

Could anybody help me to find a way around?
MY ADO library version is 2.7, I don't know if that is the problem.

Thanks!

ADO uses SQL to communicate with your database, so you need to use SQL
syntax. NZ() is an Access expression, not SQL.

The way around this depends on how you needed to use the NZ function, but
lets say you want something like "SELECT MAIL_LIST.NAME, MAIL_LIST.ADDRESS,
NZ(V.ZIP_CODE,"Unknown") FROM MAIL_LIST"; you can achieve the same result
with this SQL query:

SELECT MAIL_LIST.NAME, MAIL_LIST.ADDRESS, MAIL_LIST.ZIP_CODE AS ZIP FROM
MAIL_LIST WHERE MAIL_LIST.ZIP_CODE IS NOT NULL
UNION
(SELECT MAIL_LIST.NAME, MAIL_LIST.ADDRESS, 'Unknown' AS ZIP FROM MAIL_LIST
WHERE MAIL_LIST.ZIP_CODE IS NULL)
--
- K Dales


"salut" wrote:

I use Excel Macro to run some Access query. NZ function works fine in Access.
But it doesn't work when I was connecting to Access through Excel macro. The
error message says it doesn't recognize the "NZ" function.

Could anybody help me to find a way around?
MY ADO library version is 2.7, I don't know if that is the problem.

Thanks!

That's for answering my question!

The way I am using NZ is like the following:
Sum(a.[Result]+nz(b.[Result],0)), because table a is bigger than table b.
I am trying to use "IIf(isNull(b.[Result]), 0, b.[Result])" to substitute
the NZ function. Looks like it doesn't gave me any error message. I don't
know if it means it is correct or something else. But it is running very
slow. a query that will gave me result in 5 minutes is running for about 1
hour and it is still running.

Do you have any suggestions on my case? Thanks a lot!

"K Dales" wrote:

ADO uses SQL to communicate with your database, so you need to use SQL
syntax. NZ() is an Access expression, not SQL.

The way around this depends on how you needed to use the NZ function, but
lets say you want something like "SELECT MAIL_LIST.NAME, MAIL_LIST.ADDRESS,
NZ(V.ZIP_CODE,"Unknown") FROM MAIL_LIST"; you can achieve the same result
with this SQL query:

SELECT MAIL_LIST.NAME, MAIL_LIST.ADDRESS, MAIL_LIST.ZIP_CODE AS ZIP FROM
MAIL_LIST WHERE MAIL_LIST.ZIP_CODE IS NOT NULL
UNION
(SELECT MAIL_LIST.NAME, MAIL_LIST.ADDRESS, 'Unknown' AS ZIP FROM MAIL_LIST
WHERE MAIL_LIST.ZIP_CODE IS NULL)
--
- K Dales


"salut" wrote:

I use Excel Macro to run some Access query. NZ function works fine in Access.
But it doesn't work when I was connecting to Access through Excel macro. The
error message says it doesn't recognize the "NZ" function.

Could anybody help me to find a way around?
MY ADO library version is 2.7, I don't know if that is the problem.

Thanks!