Avoiding Null Values using nz() won't work with Access ODBC driver

[Guest]

I do a query on a crosstab in order to get my information in the format I need it

My initial problem was that the crosstab returned null values, and my query therefore returned null values as results to my arithmetic calculations. I fixed this using the nz() function

Now my problem is that when I try to refresh the ODBC query I have into excel I get a message telling me that the access ODBC driver doesn't like the nz function

Am I maybe going about this the wrong way?

Thank
Rob

 

[Brian Camire]

Instead of using something like this:

Nz([Your Field], [Value If Your Field Is Null])

try using something like this:

IIf(IsNull([Your Field]), [Value If Your Field Is Null], [Your Field])

Nz is an Access-specific function that is only "understood" in queries
executed within Access.

I do a query on a crosstab in order to get my information in the format I need it.

My initial problem was that the crosstab returned null values, and my

query therefore returned null values as results to my arithmetic
calculations. I fixed this using the nz() function.

Now my problem is that when I try to refresh the ODBC query I have into

excel I get a message telling me that the access ODBC driver doesn't like
the nz function.

 

[Guest]

That's great thanks
Ro

----- Brian Camire wrote: ----

Instead of using something like this

Nz([Your Field], [Value If Your Field Is Null]

try using something like this

IIf(IsNull([Your Field]), [Value If Your Field Is Null], [Your Field]

Nz is an Access-specific function that is only "understood" in querie
executed within Access

I do a query on a crosstab in order to get my information in the format need it

query therefore returned null values as results to my arithmeti
calculations. I fixed this using the nz() functionexcel I get a message telling me that the access ODBC driver doesn't lik
the nz function