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?

--
Jim

Not sure if this helps but you can give it a look...

http://www.dailydoseofexcel.com/arch...5/nz-function/
--
HTH...

Jim Thomlinson


"JimS" wrote:

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?

--
Jim