Query Calculation

[wendy1967]

In qryAddticket, I am trying to calculate two conditions (see Condition 1 & 2)However, I am having a problem on the first condition because it is adding 8 hours when it shouldn't. Can someone help to either fix my code or write a new one?

Condition 1: If the day_of_wk is 1, calculate time-reg_time/60, If the day_of_wk is 1 and time is less than 0, calculate time-reg_time/60+24

Condition 2: If the day_of_wk is 2, calculate time/60, If the day_of_wk is 2 and time is less than 0, calculate time/60+24

My code: pre: IIf([day_of_wk]="2",[time]/60,IIf([time]<0,([time]-[reg_time])/60+24,([time]-[reg_time])/60))

Thank you very much!

 

[locomotion]

Wendy,

It sounds as if you are having a strugle with 12 and 24 hour time which would make the time 8hours out
if you are working with time/date fields try to calculate with time definitions, and specify in your table setup whether you are dealing with 12 or 24 hour time fields

/locomotion

 

[Newman]

I think that this is what you're looking for.

IIf([day_of_wk]="1", IIf([time]<0,[time]-[reg_time])/60+24,([time]-[reg_time])/60),IIf([time]<0,[time])/60+24,([time])/60))


The translation of your code:
IIf([day_of_wk]="2",[time]/60,IIf([time]<0,([time]-[reg_time])/60+24,([time]-[reg_time])/60)) would have been:

Condition 1: If the day_of_wk is 1, calculate time-reg_time/60, if the day_of_wk is 1 and time is less than 0, calculate time-reg_time/60+24

Condition 2: If the day_of_wk is 2, calculate time/60 Something missing here