Virtually Identical Select Queries - One Works, Other Doesn't?

[BTilson]

Hello,

I am working on developing a utility that imports two spreadsheets into two tables and runs a series of queries to compare the two in order to find any changes or new records. Each spreadsheet/table is identical in structure. They are a list of orders that get sent to us by our primary customer on a daily basis. I basically import "today's" and "yesterday's" sheet. I run some simple select queries on a field by field basis to scan for any changes.

Here is the code for the query that works:

SELECT currentSheet.PurchDoc, currentSheet.Item, currentSheet.CompDesc, previousSheet.CompDesc
FROM currentSheet INNER JOIN previousSheet ON (currentSheet.Item = previousSheet.Item) AND (currentSheet.PurchDoc = previousSheet.PurchDoc)
WHERE (((previousSheet.CompDesc)<>[currentSheet].[CompDesc]));

Here is the code for the query that does NOT work:

SELECT currentSheet.PurchDoc, currentSheet.Item, currentSheet.CompMatl, previousSheet.CompMatl
FROM currentSheet INNER JOIN previousSheet ON (currentSheet.Item = previousSheet.Item) AND (currentSheet.PurchDoc = previousSheet.PurchDoc)
WHERE (((previousSheet.CompMatl)<>[currentSheet].[CompMatl]));

As you can see, except for the field they are referring to, they're exactly the same query. In my test files, the fields in "previousSheet" are blank, while they have the text "CHANGED" in the "currentSheet." The first query pulls the record just fine, but the second query pulls no results. HOWEVER, if I change the value in "previousSheet" from blank to an actual value, the broken query will then pull the result, but the functional query doesn't have that issue and it can detect a change from blank to a value.

I have checked absolutely everything I can think of trying to figure out what is causing this behavior. I have checked:

That the field formats are the same in both tables.
That the query properties are the same.
That there are no typos or inconsistencies in the SQL behind the queries.
I have relaunched Access and rebooted my computer.
I have remade the query from scratch only to get the same results.

I am running Access 2010. I know that is horribly out of date by now, but it is what we have.

Does anyone have any ideas or suggestions as to what may be going on here? I can "band-aid" the issue by running an update query to convert all blank values to something like "BLANK TEXT" for the queries, then updating those back to an actual null before exporting, but I'd really like to figure this issue out properly.

Please let me know if you need any more information. Thank you so much!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[sxschech]

maybe try:
[tt]WHERE nz(previousSheet.CompMatl,"")<>nz([currentSheet].[CompMatl],"");[/tt]

 

[combo]

Nz should solve this. Partly related to VBA, Allen Browne wrote about handling such data:

https://www.everythingaccess.com/tu...n-Nothing,-Empty,-Missing,-Null,-Zero-and-ZLS

,

https://www.everythingaccess.com/tutorials.asp?ID=Common-Errors-with-Null

combo