Weird Access bug?

[jimatqsi]

Can you tell me why this Access 2007 query returns over 21,000 rows, with 6,099 having True for the value of BinNull_YN

SELECT dbo_IM_ItemWarehouse.ItemCode, dbo_IM_ItemWarehouse.WarehouseCode, dbo_IM_ItemWarehouse.BinLocation, dbo_IM_ItemWarehouse.QuantityOnHand, IsNull([BinLocation]) AS BinNull_YN
FROM dbo_IM_ItemWarehouse
ORDER BY dbo_IM_ItemWarehouse.BinLocation;

But this query returns only 1 row

SELECT dbo_IM_ItemWarehouse.ItemCode, dbo_IM_ItemWarehouse.WarehouseCode, dbo_IM_ItemWarehouse.BinLocation, dbo_IM_ItemWarehouse.QuantityOnHand, IsNull([BinLocation]) AS BinNull_YN
FROM dbo_IM_ItemWarehouse
WHERE (((IsNull([BinLocation]))=True))
ORDER BY dbo_IM_ItemWarehouse.BinLocation;

But this query returns 6,099 rows

SELECT dbo_IM_ItemWarehouse.ItemCode, dbo_IM_ItemWarehouse.WarehouseCode, dbo_IM_ItemWarehouse.BinLocation, dbo_IM_ItemWarehouse.QuantityOnHand, nz([BiNLocation],"") AS BinNull_YN
FROM dbo_IM_ItemWarehouse
WHERE (((nz([BiNLocation],""))=""))
ORDER BY dbo_IM_ItemWarehouse.BinLocation;

Thanks for any insight into this.

Jim

[Rabbit]

Because isnull returns true when the value is equal to null. Otherwise it returns false.

Nz returns the second value when the first is null. Otherwise it returns the first value.

Let me know if you need further clarification and I can mock up a table.

[jimatqsi]

What am I missing? Why doesn't the second query return 6099 results?

[NeoPa]

Like Jim, I find queries #1 & #3 to be consistent and to provide results as expected.

What isn't clear to me is why query #2 returns only one row when, by all logic, it should return 6,099. It's not even like comparing the IsNull() result as <> False would make a difference in this case.

[NeoPa]

One thing you might like to try Jim, is to simplify and retype the second query.

For the purposes of testing much of the SQL is irrelevant. Drop that and retype to ensure none of the characters is a similar-looking one but with a different code value.

If that works as it ought to then keep adding changes and retrying until either you find the difference or you get back to what you thought you'd started with and now it's working.

[Rabbit]

Sorry, I misread the third query. You're correct, it should return the same result.

[jforbes]

Maybe I'm missing something. The Second Query will only return rows with a Null. The Third Query will return rows with Nulls and Non-Null Blank Values.

[Seth Schrock]

@Jforbes Based on the BinNull_YN field, 6099 fields do have null values and not blank values.

@Jim For your second query, try

BinLocation Is Null

as your WHERE clause. At least it would remove the function call.

[NeoPa]

JForbes:
The Second Query will only return rows with a Null.

Well, you'd certainly expect that. It's not what Jim is reporting though.

JForbes:
The Third Query will return rows with Nulls and Non-Null Blank Values.

Again, from the reported results, the same number (6,099) of records are being returned as are returned for just Nulls so, while what you say makes good sense, there is none of those in this dataset.

Seth:
BinLocation Is Null

This need not be limited to the WHERE clause of course. a better, more SQL standard way of defining [BinNull_YN] would be :

([BinLocation] Is Null) AS [BinNull_YN]

[jimatqsi]

I am going to tinker with this some. But fearing the unexplained, I replaced all of those Null values with "". I'm going to load a backup of the database (with the 6099 Nulls) so I can explore this further.

I greatly appreciate all of the comments.

Jim

[NeoPa]

I would recommend trying to work with the Nulls rather than always trying to avoid them Jim. It's your call of course, but you won't make real progress until you fully understand what you're working with, and that won't happen if you always avoid them.

[jimatqsi]

Oh, I'm accustomed to working with Null strings daily. I'm just wondering what is different about this particular situation. Inserting the empty strings was probably unnecessary, especially since all of the rows involved have zero stock on hand. But until I understand what is happening here I didn't want any process to trip over that.

Jim

[NeoPa]

That makes a lot of sense :-)

[jimatqsi]

Okay, more fun with Nulls. This is even more shocking to me.

I created these two queries. The first returns all blank/empty strings for the VABin and "NoBin" for all 5 rows because the New Jersey warehouse does not have any stock in those first 5 items. I could run with more than 5 records to get some NJ bins but I doubt it would make a difference.

The second query gives a list of all blank NJBin and a mix of real bins and "NoBin" where Null values were encountered for the Virginia warehouse.

Cool, all is well. But what happens when you union these two together?
Query 1

SELECT TOP 5 dbo_IM_ItemWarehouse.ItemCode, dbo_IM_ItemWarehouse.WarehouseCode, '' AS VABin, nz([BinLocation],"NoBin") AS NJBin
FROM dbo_IM_ItemWarehouse
WHERE (((dbo_IM_ItemWarehouse.WarehouseCode)="100"))

SELECT TOP 5 dbo_IM_ItemWarehouse.ItemCode, dbo_IM_ItemWarehouse.WarehouseCode, nz([BinLocation],"NoBin") AS VABin, '' AS NJBin
FROM dbo_IM_ItemWarehouse
WHERE (((dbo_IM_ItemWarehouse.WarehouseCode)="000"))

Each of the first two queries gives 5 rows returned. Union query returns 10 lines and all bin locations are empty strings. Does this make sense to anyone?

I read through Allen Browne's list of Access bug descriptions and there are some that stem from Null handling. But I don't see any describing this.

Here is a comma delimited listing of the 3 results.
ItemCode,Wareho useCode,VABin,N JBin
A - FREIGHT IN VENDOR,100,,NoB in
A -CC SERVICE FEE,100,,NoBin
A -DROP SHIP FRT,100,,NoBin
A -NSF FEE,100,,NoBin
A -RESTOCKING CHG,100,,NoBin
ItemCode,Wareho useCode,VABin,N JBin
-,000,A3D4,
/C,000,NoBin,
/M,000,NoBin,
/IMPORTIAP,000,N oBin,
A - COLLECTOR FEE,000,D10A2,
,,,
,,,
ItemCode,Wareho useCode,VABin,N JBin
A - FREIGHT IN VENDOR,100,,
A -CC SERVICE FEE,100,,
A -DROP SHIP FRT,100,,
A -NSF FEE,100,,
A -RESTOCKING CHG,100,,
/C,000,,
/M,000,,
/IMPORTIAP,000,,
A - GIFT CARD,000,,
A -CC SERVICE FEE,000,,

And here is the union query, which is just a copy/paste of these two queries together

SELECT TOP 5 dbo_IM_ItemWarehouse.ItemCode, dbo_IM_ItemWarehouse.WarehouseCode, '' AS VABin, nz([BinLocation],"NoBin") AS NJBin
FROM dbo_IM_ItemWarehouse
WHERE (((dbo_IM_ItemWarehouse.WarehouseCode)="100"))
union

SELECT top 5 dbo_IM_ItemWarehouse.ItemCode, dbo_IM_ItemWarehouse.WarehouseCode, nz([BinLocation],"NoBin") AS VABin, '' AS NJBin
FROM dbo_IM_ItemWarehouse
WHERE (((dbo_IM_ItemWarehouse.WarehouseCode)="000"))

And one additional, important detail. The data warehouse is MS SQL Server 2008.

Jim

[jimatqsi]

Well, I took the Top 5 limitation out of both queries and the union query now does return the "real" bin locations. It returns lots of empty strings but it does not return any bin locations of "NoBin."

Jim