Find unmatched query "null value" problem

[fishingmachine]

i have two tables. same table but different data, 1 has yesterdays data and 1 has todays data. i want to know what changed so
i ran a find unmatched query and then outer joined (i right clicked on the joins and selected the second join option) for all of the fields in both tables cause i want to compare all of the data in all of the fields.

it works except when i have a null value in 2 like fields.

ie i want 'null' from yesterdays table in field1 to equal 'null' from todays table in field1 and not show up as a difference in the unmatched query.

any suggestions on how to overcome this?

thanks,
fishingmachine

 

[tonnic]

Hi,
you have posted the same question in another thread before...

Here my answer:

Hi,

Null should always be Null, normally ...
Are you sure, that both fields have the same properties? It could be that there is a "space" in the field, and then the field has a value and is not null.

 

[fishingmachine]

hi peter,

i am sure they are they same because to test i copied my table from yesterday and just changed 1 character in 1 field to make sure the find unmatched query returned only 1 row, the row i changed.

it returned 2 rows. the row where i changed data and another row that was comparing a null cell to a null cell.

i think it has to due with the the find unmatched query using "is null" at the end of the query....

i was thinking about using about running a query that replaced any "null" value in my table with something like "unassigned" but that would not work for numeric fields.

fishingmachine...

 

[blueshark]

What version of Access are you using?

 

[fishingmachine]

access2000

i am about to try it with access 2002

 

[blueshark]

It probably would be the Is Null at the end. Is your goal to find new records, changed records, or both. For tables that I want to track changes on I usually add an extra hidden "last changed" field that fills in the time and date when the record is changed.