If-Then-Else Statement

[NicholasFunk]

I have been using Access for about a month and I have figured out through trial and error how to do a simple "If-Then" statement. I am now trying to add a third option to the mix and this has been my down fall. I am looking for the following formula for Access 2010 query: If = "PCS/MIN", then (Formula 1), if = "PCS/HR", then (Formula 2), if = "MIN/PC", then (Formula 3). I will be dividing one cell by another if that helps any. I have never posted anything like this before so I am not sure what other info is needed to help me. Thank you for any help you can give.

 

[Brianwarnock]

You could use an if then else block with ElseIf in there but I would look at the Select Case option.

Brian

 

[pbaldy]

Depending on how many, you can nest one IIf() function inside another, or check out the Switch() function.

 

[Brianwarnock]

Oops! With it being titled If Then Else assumed VBA module , hadn't noticed it was queries.

Brian

 

[NicholasFunk]

Thanks for you replies, but I really don't know what you are saying. Like I said, I have been doing this for a month. Here is what I have in the Expression Builder. What is wrong with it(I am sure a lot!)?
If [RUN_TYPE] = "PCS/HR" Then [GOOD_QTY]/[HOURS_WORKED]
Else If [RUN_TYPE] = PCS/MIN" Then [GOOD_QTY]/([HOURS_WORKED]*60)
Else If [RUN_TYPE] = "MIN/PC" Then ([HOURS_WORKED]*60)/ [GOOD_QTY])
End If

 

[SmallTomato]

Why not just a nested IIF?

IIF([RUN_TYPE]="PCS/HR", [Good_Qty]/[Hours_Worked],iif([RUN_TYPE]="PCS/MIN",[Good_Qty]/([Hours_Worked]*60),iif([RUN_TYPE]="MIN/PC",([Hours_worked]*60)/[Good_Qty])))

 

[Brianwarnock]

More like , but syntax may need tweaking
IIf ([RUN_TYPE] = "PCS/HR" ,[GOOD_QTY]/[HOURS_WORKED],IIf ([RUN_TYPE] = PCS/MIN", [GOOD_QTY]/([HOURS_WORKED]*60),IIf( [RUN_TYPE] = "MIN/PC" , ([HOURS_WORKED]*60)/ [GOOD_QTY])))

Brian

I see small tomato beat me to it

 

[NicholasFunk]

Thank you guys so much. This worked perfectly. I did not realize I could write it that way, or what a nested IIf was. You guys rock!!

 

[Brianwarnock]

In queries you use the Immediate If ie IIF , the If...Then.. Else.. End if is used in VBA and can contain Multiple ElseIf , but worry about that when you get there.

As PBaldy pointed out you could use the Switch function, and it is worth reading about that in help as nested Iifs can get difficult to read and also have a limit.

Brian

 

[NicholasFunk]

Sooo....now I am getting #DIV/0! in a few cells. I tried adding an IIf statement that read IIf[HOURS_WORKED]or[GOOD_QTY]="0","0". I put it as part of the nest, by itself, even added [HOURS_WORKED]/[GOOD_QTY]or[GOOD_QTY]/[HOURS_WORKED]at the end of the statement, as I found it on the help area. Nothing worked. I cannot run reports, I assume it is due to this error? Plus it is just annoying to have that error.

 

[SmallTomato]

There might be a better way to do this....
IIF([RUN_TYPE]="PCS/HR" and Hours_Worked<>0, [Good_Qty]/[Hours_Worked],iif([RUN_TYPE]="PCS/MIN" and Hours_Worked<>0,[Good_Qty]/([Hours_Worked]*60),iif([RUN_TYPE]="MIN/PC" and Good_Qty<>0,([Hours_worked]*60)/[Good_Qty],0)))

These are all 0s in the table correct and no nulls or blanks?

 

[NicholasFunk]

THANK YOU SmallTomato!! It worked like a charm.

 

[SmallTomato]

You can change the last 0 to whatever you want to be displayed when all the IF functions return false (without seeing your data, I am assuming is only when you are dividing by zero)