Comparing Tables

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Is there anyway to compare tables and generate a report of all the
differences? We have sensitive data that gets input 2 times to ensure
accuracy of the data entry. Then each field (74 fields) gets compared
(manually) to verify accuracy. There has got to be a quicker way to
automate the double data entry and run a report against the two tables.
Currently the table structure of the 2 tables is identical.

Thanks,


Jeff
 
WOW, thanks for you help. That ROCKS!

Except it doesn't pickup discrepancies where one field is null and the other
is not null. Is there any way around that?

Thanks again,
 
Good question! I'd forgotten about Nulls completely. The problem comes in
when you try to compare a value with Null. You can't use the = or <> signed
when trying to compare fields that may have Nulls in them. It will ALWAYS
return false. So this line:
If rstBase(fld.Name) <> rstVarying(fld.Name) Then
Will return false even if rstBase(fld.Name) has a value but
rstVarying(fld.Name) is Null.

The solution is to use the NZ function around the fields that could contain
values. Like this:
If Nz(rstBase(fld.Name)) <> Nz(rstVarying(fld.Name)) Then

If you want to write the value of <Null> to your comparison table, you also
have to use the NZ function there too:
" has changed from " & Nz(rstBase(fld.Name), "<Null>") & _
" to " & Nz(rstVarying(fld.Name), "<Null>")

Similar changes have to be made to CompareTables2. I could list the changes
here, but I've modified the sample and put the new version up on my website.

Thanks for finding this glitch!

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top