export nz?

[spinkung]

Hi,

I have a query that uses the nz function to 0 some error values. I want to use the query as a source of data in an excel file but excel doesn't recognize the nz function and has decided to throw it's toys out of the pram until I change it.

Does anyone know an alternative, excel friendly, way to clear the errors? oh, I can't just leave then in because excel doesn't like that either.

Thanks,
Spin.

 

[Uncle Gizmo]

Leave the Query with the nz function in, in access and then query that query from excel.

 

[boblarson]

or instead of using the NZ function in the Access query, you can use an IIF. It is longer but might work better.

NewFieldName:IIF(IsNull([FieldName]), 0, [FieldName])

 

[spinkung]

thanks both,

just got back round to this.

ok so.....

I now have a field that comes back with #error as a value and guess what, excel doesn't like this either.

Can i replace this with a 0 somehow??

Thanks,
Spin.

 

[boblarson]

Are you sure it is just NULLS causing the errors you said in the first post? Nz will only handle NULLS but if you have divide by zero or other errors then it won't help.

Also, sometimes, and I am not sure why, but if I take that query and make another query out of it so that the zeros are just part of it without the formula it sometimes works. (same goes with some calculated fields)