Multiple Cases in Where Statement

[Pisteuo]

I have multiple cases that includes multiple "true" results. I am confused about the best method to proceed with my query SQL. Should I use nested IIf's or does Jet SQL support cases? What is the correct syntax for multiple "true" results?

There are three cases:
Condition A, Condition B, Condition Constant

Case A
When "A"
Then filter "A" and "Constant"
Else Case B

Case B
When "B"
Then filter "B" and "Constant"
Else Case C

Case C
When "C"
Then filter All
End

Thank you for the help.

 

[vbaInet]

I don't completely understand your question but perhaps this will aide you:

LIKE IIF([Field] = 'C', "*", [Field]) & " OR " & LIKE IIF([Field] = 'C', "*", Constant)

Perhaps you want to elaborate withs some sample results and your expectations.

 

[Pisteuo]

I probably confused terminology by interchanging "constant" with criteria C. A simplified explanation follows:

I have three cases for filtering my query. The user can choose either A, B, or C.

If the user chooses A, then the query filters by A & C
If the user chooses B, then the query filters by B & C
If the user chooses C, then the query filters by All (A, B, & C)

Case A
When "A"
Then filter "A" and "C"
Else Case B

Case B
When "B"
Then filter "B" and "C"
Else Case C

Case C
When "C"
Then filter All
End

 

[vbaInet]

What are A, B and C? Values in a specific field?

 

[Pisteuo]

Yes. A, B, and C are the only values found in the field being filtered.

The user will designate if they are an A, B, or C. The query will return records based on the choice. If the user chooses A, the query will return records with A or C in that field.

 

[vbaInet]

That's the code I gave you in my first post then.

 

[Brianwarnock]

I didn't follow vba's solution, but I'm sure it works , however I would use

WHERE ([param]="C") OR (yourfield=[param] Or yourfield="c")

param is either the pop up prompt or can be expanded to a form field which ever you are using.

Brian

 

[Pisteuo]

vba / Brian,

Just to make sure we are on the same page that I'm looking for an SQL statement, not VBA.

My intent is to simply filter a query.

SQL is giving an error message that a comma should come before LIKE after the concatenate.

Thanks for the continuing help.

 

[Pisteuo]

Here is the code I used. Your help definitely assisted me. Thanks.

  (
    (tblCompetency.fldConcentrationID) like IIf([tblCoachee].[fldConcentration]="General","*",[tblCoachee].[fldConcentration]) OR  (tblCompetency.fldConcentrationID) = "General"
  )

 

[vbaInet]

Happy to see you got this resolved.