Compare fields in Access

Hello,

I'm using to Access to perform my column compares and I'm having some issues. Do you have some recommendations?

I was going to use the nz function but I dont know how reliable it is.

Thanks
lulubell-bAsked:
Who is Participating?
 
GRayLCommented:
Because you said you are comparing columns in different tables it should look like this

Nz(tblBefore.fld2Compare,0)<> Nz(tblAfter.Fld2Compare,0)
0
 
Bryan ButlerCommented:
Please clarify that you are comparing columns in your Access DB tables.  Also, what nz function are you referring to?  (the return value one?)  What is the error/issue you are having?  
0
 
lulubell-bAuthor Commented:
I'm comparing columns in my access tables. I need to compare data of old vs. new. and I need to see any differences that occurred.

nz([AFTER].[01]))<>([BEFORE].[1])

I'm just unsure if this is the best way to go. I need to compare each column within the table.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
GRayLCommented:
Nz(After,0)<>Nz(Before,0)
0
 
GRayLCommented:
BTW, I've found Nz() is very reliable - however, I can't say the same for some of the users ;-)
0
 
Bryan ButlerCommented:
I assume you are looping through result sets using NZ.  But would a query work for you?

select * from before B, after A where B.fld2Compare != A.fld2Comapare
0
 
Bryan ButlerCommented:
Oh, and you need a key...

select * from before B, after A where B.fld2Compare != A.fld2Comapare  and B.key = A.key
0
 
GRayLCommented:
'!=' doesn't fly in Access.  

In the Immedidate Pane:

? 1<>2
True
? 1!=2
False
0
 
Bryan ButlerCommented:
Thanks GRayL.  So:

select * from before B, after A where B.fld2Compare <> A.fld2Comapare  and B.key = A.key
0
 
lulubell-bAuthor Commented:
Can I perform an outer join with it too? I would like value that do not exist New but existed in the Old

Thanks
0
 
lulubell-bAuthor Commented:
I also noticed that in the before its lowercase and uppercase in the after.

How do I add the UCASE function to the NZ function?

Thanks
0
 
lulubell-bAuthor Commented:
I figured out the rest, thank you
0
 
GRayLCommented:
Thanks, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.