CODEWITHSUNDEEP
ms-access
bd528
bd528

Reputation: 886

Comparing more than 2 fields in Access query

In an Access query, if I want to compare 2 fields, I do something like :-

IIf([Field1] = [Field2], "Yes", "No") AS [MATCH]

Is it possible to compare 3 fields, and if all the 3 values are different then result should be Yes, otherwise the result should be No.

Upvotes: 0

Views: 3350

Answers (4)

GoodJuJu
GoodJuJu

Reputation: 1590

Yes, you can just expand your logic with the logical AND operator:

IIf(([Field1]=[Field2] And [Field2]=[Field3]),"Yes","No") AS [MATCH]

Note though that in your question you said, 'if all the 3 values are different then result should be Yes, otherwise the result should be No'. This contradicts your SQL query that has 'Yes' where the values are equal. Switch your "Y","N" around to suit....

This will return:

|-------|-------|-------|-------|
|Field1 |Field2 |Field3 |Result |
|-------|-------|-------|-------|
|1234   |1234   |1234   |Y      |
|1234   |aabb   |aabb   |N      |   
|1234   |we     |1234   |N      |
|-------------------------------|

Upvotes: 0

forpas
forpas

Reputation: 164204

If any 2 of the 3 fields are equal, you want the result to be "No".
So use the OR operator:

IIf(
  [Field1] = [Field2] OR [Field1] = [Field3] OR [Field2] = [Field3], 
  "No", 
  "Yes"
) AS [MATCH]

Upvotes: 1

CeeJay1337
CeeJay1337

Reputation: 11

GoodJuJus answer is almost there.

IIf(([Field1]=[Field2] AND [Field2]=[Field3] AND [Field1]=[Field3]),"No","Yes") AS [MATCH] This will allow only cases where all three fields are different.

If you want to accept cases with 2 (but not 3) identical values this will not work.

Anyway inverting the logic might make it nicer to read:

IIf(([Field1]<>[Field2] And [Field2]<>[Field3] And [Field1]<>[Field3]),"Yes","No") AS [MATCH]

Upvotes: 1

tmsdajpa
tmsdajpa

Reputation: 62

Here are 2 ways to do it:

In my examples, I have it set to "yes" if there's a match, so you may have to tweak it. I'm unsure if you really want it to say "yes" if it's not a match.

Using the Macro Builder

Using the Macro Builder

In case you want to use VBA:

Private Sub Command20_Click()
If Text1.Value = Text2.Value Then
step1 = "yes"
End If

If Text1.Value <> Text2.Value Then
step1 = "no"
End If


If Text1.Value = Text3.Value Then
step2 = "yes"
End If

If Text1.Value <> Text3.Value Then
step1 = "no"
End If


If step1 = step2 Then
Label25.Caption = "yes"
End If

If step1 <> step2 Then
Label25.Caption = "no"
End If

Upvotes: 0

Related Questions