query to test if fields have the same value

Hello Experts,

I'm try to create a calculated field in a query that tests if three fields have the same value in the same records and I'm stumped.

I'm using Access 2010

Thanks
LVL 3
eshurakAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Do you mean this:

SELECT Table1.FIELD0, IIf([FIELD1]=[FIELD2] And [FIELD1]=[FIELD2] And [FIELD2]=[FIELD3],"YES","NO") AS Expr1
FROM Table1;

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Why not do that in the WHERE clause instead ?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Example:

SELECT Table1.*
FROM Table1
WHERE (((Table1.FIELD1)="abc") AND ((Table1.FIELD2)="abc") AND ((Table1.FIELD3)="abc"));

If not, what do you have so far?

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
eshurakAuthor Commented:
The values will never be the same.

Also It needs to be a calculated field.  Something like:
Expr1: ([fld_2007]=[fld_2008])=([fld_2007]=[fld_2009])=([fld_2008]=[fld_2009])
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Not following:

"that tests if three fields have the same value "

"The values will never be the same."

?
0
 
eshurakAuthor Commented:
Let me clarify:  the value we are looking for will never be the same.  In other words we can't test for "ABC" like you did in your second post.  The values in my fields could be any numeric combination (it is a text field as there can be leading zeros).
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok ... did you see my post @ http:#a37229162  ?

mx
0
 
eshurakAuthor Commented:
That works.  I shortened it to:

=[fld_2008]) And ([fld_2007]=[fld_2009]) And ([fld_2008]=[fld_2009])

Thanks

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
cool.

mx
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.