If statement in a query?

[afs24]

How do I write the following statement given this scenario?

If the feild WORK FUNCTION is equal to CARTPICK then I want to divide the feild CARTONS by HOURS. If the feild WORK FUNCTION is equal to FULLPICK then I want to divide the feild TRANS by HOURS. If WORK FUNCTION equals neither then I want it to be zero. So it should look something like this, I think:

CPH:if([WORK FUNCTION]=CARTPICK,"[CARTONS]/[HOURS]",IF([WORKFUNCTION]=FULLPICK,"[TRANS]/[HOURS]",""))

Is this correct???

 

[bmacr]

You need to use Iif in Access instead of If. (Stands for Immediate If)

Something like this:

CPH: Iif([WORK FUNCTION]=CARTPICK,[CARTONS]/[HOURS],Iif([WORKFUNCTION]=FULLPICK,[TRANS]/[HOURS],0))

I changed your "" to 0, since you said you wanted it to return zero. "" returns a zero-length string.

Hope it works.

 

[afs24]

Any idea why the query asks me to enter CARTPICK, WORK FUNCTION, & FULL PICK in a pop up when I run the query?

 

[afs24]

It asks to enter the parameter value for each.

 

[bmacr]

I assumed those are all fields in your query. It's having trouble finding them. Are they all spelled correctly? I notice now that in one place you have [Work Function] and in the other you have [WorkFunction] (no space). Which one is the correct one?

 

[afs24]

I feel like a dummy now! Problem fixed. thanks