Why does zero not equal zero in my update query in Access?

[Colin Kepl]

In Access, I have a table that receives data from Excel. I have a second table that has one of its fields changed based on a comparison to the first table. The update query is as follows:

UPDATE OpenReqs 
LEFT JOIN DownloadedReqs 
ON (([OpenReqs].[SUF]=[DownloadedReqs].[SUF]) 
    Or ([OpenReqs].[SUF] Is Null)) 
  AND (([OpenReqs].[DOC NO]=[DownloadedReqs].[DOC NO]) 
    And ([OpenReqs].[STOCK NO]=[DownloadedReqs].[STOCK NO])
    And ([OpenReqs].[QTY]=[DownloadedReqs].[QTY]) 
    And ([OpenReqs].[TRR]=[DownloadedReqs].[TRR])) 
SET OpenReqs.SHIPPED = Yes
WHERE (([DownloadedReqs].[DOC NO]) Is Null);
 

This works like a charm as long the QTY values in both tables are non-zero. If both tables have a zero value, it returns false and marks as shipped. Why does zero not equal zero? There does not seem to be a rounding error either (I've looked at the Excel data out to 35 decimal places, and there are no calculations performed on the QTY value). I've tried text and number data types in Access, and I've tried changing the data types in Excel to match the Access fields before the import. No dice. What am I missing?

Thank you.

[code green]

Are you sure the values are zero and not empty or NULL?

[Colin Kepl]

Values in the source Excel workbook are 0. And when I open the table in Access (Access 2007) using DataSheet view, I can filter for 0 in the QTY field. I can select "equals 0", "does not equal 0", etc., so I'm assuming it's being recognized as a zero. If both tables' QTY values are 1, 2, 3 ... n, they are correctly NOT marked shipped, but if both tables have a zero (0), they are marked shipped. I don't have any kind of validation/formatting requirements on the field from what I see in the field properties.

Thanks for helping.

[NeoPa]

Considering what you've already told us this is a long-shot, but have you confirmed all the xeroes are actually numeric zeroes and not string zeroes? I normally tell the difference by ensuring the Horizontal Alignment is set to General. That separates the wheat from the chaff.

As I say - a long-shot - but I hope it's some help :-)

[code green]

This worries me

I've looked at the Excel data out to 35 decimal places

It suggests a floating point value.
You cannot ever, ever, ever compare one or more floating point numbers.
This is down to how a float is represented and is a full lesson in computer science.

The fact you get away with it when not zero may be a fluke.
I apologise if there is no float in there, but if so you either need to

CAST or CONVERT [OpenReqs].[QTY] and[DownloadedReqs].[QTY] to INT.

Another way is subtract the two and compare to a tolerance.

And (([OpenReqs].[QTY]-[DownloadedReqs].[QTY]) < EPSILON)

where EPSILON is something like 0.00001.

Like I said, this is a big subject but it still trips a lot of people up (including me)

[Colin Kepl]

code green,
I used your first method and it works. Thanks! Perhaps the Excel data is casuing a floating point issue..
NeoPa, I'm almost sure they were not string (but I was doubting everything I was seeing on the screen).
Thanks to you both for the replies and successful assistance. Cheers!

[NeoPa]

If that method worked for you then I'm pretty sure it's not string related too. It was a thought - that just happened to be wrong in this case. Luckily Code Green stepped up to sort you out. That's how it goes sometimes :-)