Hi,

I am trying to create a query that if a certain field type is picked then it will return another field value as follows.

I am creating a new field called Rate the value of which all depends on what shift type is added on the order:=

If [Shift Type] = Days then the Rate = [Rate Days]

There are five different shift types
Days
Nights
Van
Overtime
Bridge Toll

so therefore are five different shift rates.

I have tried every which way would really appreciate some help.

Many thanks

As a field in the query:

Rate: IIF([Shift Type] = "Days",1,
IIF([Shift Type] = "Nights",2,
IIF([Shift Type] = "Van",3,
IIF([Shift Type] = "Overtime",4,
IIF([Shift Type] = "Bridge Toll",5,null)))))

Just replace the numbers with the values you would like for Rate.

I think what he means is that he is storing all of the rates in each record and if the shift is say "Days' then he wants the value in the 'day_rate' field, etc

Thanks so much - its easy when you know how!!!

I think what he means is that he is storing all of the rates in each record and if the shift is say "Days' then he wants the value in the 'day_rate' field, etc

.
.
.
Or not :p

:cool::cool::cool: