Nz(), COALESCE problems

[gabrielcardier]

Hello folks, I´m very urged with this topic. I´m importing info from an ODBC and in a particular column (once imported the data) named QUANTITY, certain fields show null values. My solution was to use the Nz () function in the Design View as follows:

Expr1: Nz([Quantity], "0")

A new column (named Expr1) is shown when ran the Query. This column contains "0" values instead of "null" values as desired, more often when saving changes to the query a warning Invalid argument message appears, forbidding me from saving the data.

I have tried COALESCE and IsNull functions as well, but it seems that Access 2003 does not support these functions, at least mine does not.

Copy Paste are no options because of the massive amount of info.

I need to export the data to another program called JMP (a Statistics program) which does not recognise the Nz() function as well.

Please help me out.

A desperate Colleague!

 

[boblarson]

create an update query to update the null values in the field to 0.

 

[gabrielcardier]

How can i do that,? im pretty new when it comes to access and querys

 

[boblarson]

 

[pbaldy]

Don't forget a criteria!

 

[boblarson]

OOPS! Yep, in the criteria put IS NULL

 

[pbaldy]

Nice demo though; much nicer than I would have cobbled together. I like the shadow effect.

 

[Rich]

Remove the quotes surrounding the 0 in your expression

 

[dukwei]

great quick tutorial boblarson! i'm a total newb so that will help in the near future im sure. thank you