MSQuery and NZ()

J

JimS

I have a stored query in Access I use to export a "flattened" version of my
highly normalized database. It's exported to excel.

I have recently taught my users to use MS Query from excel, targeting the
stored "flattened" query. Unfortunately, due to the prevelance of nulls in
certain field used for calculations, I use the NZ() function alot in the
"flattened" query. It calculates dollars from the sum of several
multiplications, so it's important to be there in exactly the right way for
consistency. I could force the user to do the calcs in Excel, but I'm certain
they'd lose consistentcy and rounding.

Example:

TotalDlrs:
CCur(Round(Round(nz([stdhours])*nz([strate]),2)+Round(nz([othours])*nz([otrate]),2)+Round(nz([dthours])*nz([dtrate]),2),2))

If any of the fields (rates, hours) were null, the whole equation comes out
to 0 (null).

When my users target my "flattened" query from excel, they get an error
saying the "nz()" function is not found. How can I overcome this?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top