Using MS Access/Access 2007

Advertisement


Question
Images
Images  
QUESTION: Hello Scottgem,

I have created a database which compares one field in a table against another field in a separate table and if the fields do not match an error is appended to an error log table. What I have noticed is when there are Null fields on one table, but the field on the other table has a value in the cell I am not getting that error. My code is this [table1]![street2] <> [table2]![street2].


When I try putting in my criteria of [table1]![street2] <> [table2]![street2] or IS NULL I am getting everything for those fields. Example below I would expect to see clock number 111114 since the field in table1 does not match the field in table2, but not the others. Is there a way to have the query pull back the information if the field that is blank on one table and not on the other, without getting the ones where the fields are blank on both tables?

I can provide a copy of the database if this will be helpful.

Thank You,
Kathleen

ANSWER: How are you comparing the fields? In a query on a form, in code? If in a query, can you post the SQL for the query, if in code, the code snippet and what triggers it. if on a form can you prost the control source of the control where its being done.


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: The compare is within a query, here is the sql

INSERT INTO tblErrorLog ( Clock, PSID, [Last Name], [First Name], [Compare Type], [Error Type], [Master Value], [TOS Value] )
SELECT tblTOS.Clock, tblTOS.PSID, tblTOS.[Last Name], tblTOS.[First Name], "tblMASTER.City to tblTOS.City" AS Ctype, "City Mismatch" AS Etype, tblMASTER.City, tblTOS.City
FROM tblTOS INNER JOIN tblMASTER ON tblTOS.Clock = tblMASTER.Clock
WHERE (((tblTOS.City)<>[tblMASTER]![City]));

Thank You,
Kathleen

Answer
That's the way I would have done it. But if there are nulls in either table try doing it this way:

WHERE Nz([tblTOS].[City],"")<>Nz([tblMASTER]![City],"");

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.