NZ Function and Null trap

[jpstory]

Hi All

I am running into a strange situation here within my query built in Access.

The query INNER JOIN 2 other queries ( there are only 3 identical fields in each query: Account, Buy, Sell) to track the difference of Buy and Sell (populated with numbers or blank) between the 2 queries.

Understood that Null is not equal to Null in Access, therefore I used NZ function to force all the blanks into number '0'.
However, it worked on most of the records (group A) but did not work some other records (group B).
What was strange was that, If I did not use NZ function at all, then the result would flip: it worked on group B but not on group A.

I double checked the data type, for both queries (they both ran on tables linked to Excel spread sheets), Buy and Sell was either populated with number or blank (no space)

Here is the query,

SELECT TblA.Account,  TblA.Buy, TblA.Sell, TblB.Buy, TblB.Sell 
  FROM TblA INNER JOIN TblB 
    ON TblA.Account = TblB.Account
 WHERE NZ(TblA.Buy,0) <> NZ(TblB.Buy,0) or NZ(TblA.Sell,0) <> NZ(TblB.Sell,0);

Can anybody see what's wrong here, thanks!

 

[Joe4]

What is the Data Type of these Buy and Sell fields?
Note that the NZ function returns a Text value, so if the Buy and Sell fields are numeric, you may be comparing Numbers and Strings.
You can coerce NZ to return numeric value like this:

(NZ(TblA.Buy,0)+0)

So you might need to make that change to those calculations in your WHERE clause.

 

[jpstory]

Hi Joe

Thanks for looking at this, I tried your suggestion to force NZ to return a numeric value, it did not work.

To answer your question on the Data Type, in the original excel spread sheet, they are 'General', then a query is run to select the qualified records on which the query I posted above then is run.
(The tables are linked to Access not imported, not sure if it matters)


What is odd is that how come only some of the record fell off, I would expected the query fail completely ... ... I mean the Data Type gotta be the same for records within the same field/column right?

 

[Joe4]

If the data is linked from Excel, it might not be interpretting those blanks as NULLs at all. Test it and find out.
If you run the following query, do the final two columns have values for every single record?

[COLOR=#333333]SELECT TblA.Account,  TblA.Buy, TblA.Sell, [/COLOR][COLOR=#333333]NZ(TblA.Buy,0) as A_Buy, [/COLOR][COLOR=#333333]NZ(TblA.Sell,0) as A_Sell[/COLOR][COLOR=#333333]
[/COLOR]FROM TblA;

If not, Access is not seeing those "blank" values as Nulls, and hence the NZ function will not work on them.

You probably want to run the same query concept on your other table too to check that as well.

 

[xenou]

If you are comparing doubles (decimals) it's not a good idea to use equals. They can be different by very small amounts (+- .00000000000001) and so not be equal even though you would probably want them to be. These numbers could be stored in Excel as doubles even if they have no decimals visible.

 

[jpstory]

Hi Joe

I tested both tables, the results are the consistent: the final two columns both have values populated for every single record. Cells where it is previously blank now are populated with 0 ( zero ) .

- Xenou
Thanks for looking at this too, the values populated are all whole numbers (no decimals at all), therefore I believe it won't be recognized by Excel or Access as decimal.

 

[xenou]

Thanks for looking at this too, the values populated are all whole numbers (no decimals at all), therefore I believe it won't be recognized by Excel or Access as decimal.

I wouldn't

Excel has no data typing like Access does to define a value as an Integer.