nz problem from access to excel

[timely2]

I am trying to call data to a excel table from a query in Access but it says it cannot recognize the nz in excel. Is there another way to move the data so it recognizes blanks as a zero from access?


This is the query:

SELECT test.pickerNum, test.period, test.week, test.year, sum(test.olq) AS total, sum(nz([test2].[casesAdjusted],0)) AS qw, round((total-qw)/total*100,2) AS score
FROM test2 RIGHT JOIN test ON test2.invoice = test.invoice
GROUP BY test.pickerNum, test.period, test.week, test.year;


In excel I just do an sql = the field names and it gives error of:

Run-time error '-2147217900 (80040e14)':
Undefined function 'nz' in expression.

Thanks,
Tim

 

[PHV]

You may try something like this:
Sum(IIf(IsNull(test2.casesAdjusted), 0, test2.casesAdjusted)) AS qw
or like this:
Sum(Val(test2.casesAdjusted & "")) AS qw

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[timely2]

Thanks, that worked great!

Tim