CODEWITHSUNDEEP
ms-access
user1374746
user1374746

Reputation: 11

Access Query If/or condition

I'm trying to write an OR statement in the query design in Access and its not working! The statementis below:

Duration:If([Status2]="Q","2.5 Hours",IIf([Status2]=WS,"4 Hours", IIf([Status2]=MG,"3 Hours 15 Mins",IIf([Status2]=ID,"3.5 Hours"))))

Upvotes: 1

Views: 6042

Answers (2)

HansUp
HansUp

Reputation: 97131

As Zaider89 pointed out, you left off an I from the first IIf().

Also you're comparing [Status2] to 4 different values: Q; WS; MG; ID. You enclosed the first of those values with quotation marks, but not the others. That makes me suspect the db engine would treat the unquoted values as parameters and ask you to supply values for WS, MG, and ID.

The last of your IIf() expressions includes a "true part" but no "false part":

IIf([Status2]=ID,"3.5 Hours")

You could fix those problems, but I suggest you consider using the Switch Function instead. Try the following on a single line in the query design grid in a column next to your IIf() attempt. 

Duration:Switch([Status2]="Q","2.5 Hours",
[Status2]="WS","4 Hours",
[Status2]="MG","3 Hours 15 Mins",
[Status2]="ID","3.5 Hours")

That Switch() approach looks better to me. However I think it would be better still to create a separate look-up table with one column for [Status2] and another for the mapped value. Then join that table into your query. After you create the table, it's easy to use it in your queries and I think it's easier to understand than approaches using Switch() or nested IIf() expressions. It would also be easier to extend when you need to add new conditions --- just add them as rows in the look-up table.

Upvotes: 1

Zaider89
Zaider89

Reputation: 1

If that is a direct copy from your access database you are missing an I on the If at the beginning. it should be IIF not IF.

Upvotes: 0

Related Questions