Query Calculation trouble

[Zippyfrog]

Hey, how can I do a calculated field in a query based off another field in the same query?

Here is what I have:

DayID (number)
Payment (number)

I want the calculated field TotalPayment to be changed based off of the dayID.

So, if the DayID = 1, then I want 100 added to Payment
If the DayID = 2, then I want 200 added to Payment

Here is a sample of what I want the query to look like:

ID EmployeeID DayID Payment TotalPayment
1 1 2 150 350
2 1 1 145 245

etc..... (so the amount added is based off of the DayID in that row)

How would I go about doing this? I tried an If then statement, but that doesn't work in a query. I also tried a SelectCase. Any help would be greatly appreciated! Thanks in advance.

-Chris

 

[Jon K]

In query Design View, put this in the Field: cell of a column:-

TotalPayment: [Payment]+IIF([DayID]=1,100,200)

 

[Zippyfrog]

Thanks for the help. That works fine as long as I only have 1 condition. What would I do if the DayID is either 1 or 3 or 4, then add 100; and if DayID is 2 or 4 then add 240? I tried putting parenthesis around the expression then adding "or" but to no avail:

TotalPayment: [Payment]+IIF([DayID]=(1 or 3 or 4),100)

What would be the correct syntax for that?

 

[Jon K]

You can nest IIFs in the expression e.g.

[Payment]+IIF([DayID] in (1,3,4), 100, IIF([DayID] in (2,5), 200, 0))

meaning:-
if DayID equals to 1, 3 or 4, add 100
else if DayID equals to 2 or 5, add 200
otherwise add 0

 

[Zippyfrog]

Thanks so much!!! That line of code works great!

Now, one last question about it: I have one more field, called authorized, and if it is authorized the value is one, and if it isn't authorized, the value is 2. Now, how would I make it so that it only adds when the authorized value is one?

I thought it would be something like this (green is how I modified the code):

[Payment]+IIF([Authorized] in (1), IIF([DayID] in (1,3,4), 100, IIF([DayID] in (2,5), 200, 0)),0)

However, that didn't work. Any other ideas?

Once again, thanks for all your help!

-Chris

 

[Jon K]

[Payment]+IIF([Authorized]=1 And [DayID] In (1,3,4), 100, IIF([Authorized]=1 And [DayID] In (2,5), 200, 0))