We help IT Professionals succeed at work.

query to test if fields have the same value

eshurak
eshurak asked
on
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
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Why not do that in the WHERE clause instead ?

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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

Author

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])
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Not following:

"that tests if three fields have the same value "

"The values will never be the same."

?
Database Architect / Application Developer
Top Expert 2007
Commented:
Do you mean this:

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

mx

Author

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).
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ok ... did you see my post @ http:#a37229162  ?

mx

Author

Commented:
That works.  I shortened it to:

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

Thanks

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
cool.

mx