NZ

[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.

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?

Should I post this in the excel forums instead?

 

[fredg]

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.

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?

Should I post this in the excel forums instead?

Perhaps you should post in an Excel group if this doesn't help.

Excel doesn't know about the Access Nz function.
Most likely neither does MSQuery.
Setting a reference, in Excel, to the MS DAO 3.6 object library
doesn't seem to help, and I have no idea which library contains it.

Try exporting from Access to Excel using the TransferSpreadsheet
method, instead of importing into Excel using MSQuery.
That will work, I just tried it.

DoCmd.Transferdatabase acExport, acSpreadsheetTypeExcel9,
"qryEportNz", "c:\MyTestFolder\MySpreadsheet.xls", False

The above will transfer the query "qryEportNz" to a worksheet named
"qryExportNz". You can do whatever you want with it once it's there.

 

[JimS]

TransferSpreadsheet isn't viable because it transfers a massive amount of
data and exceeds Excel's row limit. It's what we've used in the past, and
still use for defined subsets of the data.

 

[George Nicholson]

Setting a reference, in Excel, to the MS DAO 3.6 object library

doesn't seem to help, and I have no idea which library contains it.

NZ() should be in the Access Object library (that's why VBA and DAO know
nothing about it). No idea if setting a reference to that library will solve
the issue though.

I recall running into the same issue a few years ago and I believe I ended
up using Automation to have Excel create an instance of Access that opened
the db and its query and then used GetRows(?) to populate a worksheet.
Probably a more elegant way of doing it, but I stopped looking for solutions
once I found one that worked and the issue hasn't popped recently.

 

[JimS]

I bit the bullet and changed the nz() to iif(isnull([xxxxx]),0,[xxxxx]). I
feared it would cause performance issues, but not so far. I'm up to 98,000
records now. It's only been a year, and it's accelerating. The bubble will
burst eventually.
--
Jim

Setting a reference, in Excel, to the MS DAO 3.6 object library
doesn't seem to help, and I have no idea which library contains it.

NZ() should be in the Access Object library (that's why VBA and DAO know
nothing about it). No idea if setting a reference to that library will solve
the issue though.

I recall running into the same issue a few years ago and I believe I ended
up using Automation to have Excel create an instance of Access that opened
the db and its query and then used GetRows(?) to populate a worksheet.
Probably a more elegant way of doing it, but I stopped looking for solutions
once I found one that worked and the issue hasn't popped recently.

 

[John Spencer]

I would write the statement using

IIF([xxxxx] is null, 0, [xxxxx])

IF that works it should be more efficient than calling a vba function to
determine is the field is null or not.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================