Table calculated field help

[jcaptchaos2]

I have a calculated field in a table and what I need it to do is to look at the Time On and the shift and if the shift is "3" and the time on is between 7am and midnight to take the date and add 1 day to it. Here is what I got but it does not seem to add a day no matter what I have in for a time. If I remove the and part of the formula and just use shift equals "3" then it works fine. Here is the code.

IIf([shift]=3 And [Time On]>7 And [Time On]<24,[date]+1,[date])

Thanks
JC

 

[PaulO]

Not sure that adding a calculation field into a table is the correct approach ... others may of course disagree.

Personally I would use an Update query, using the same expression/logic, it's certainly worth trying.

 

[gemma-the-husky]

what datatypes are [time on] and [shift]

that may be the key to this problem.

 

[Rabbie]

Calculated felds in tables are not generally a good idea. See this link for a detailed explanation

 

[jcaptchaos2]

All,

Thanks for your reponses, here is what I got to work this morning, our 3rd shift starts at 10pm the date prior to the actual pay date so this will move the shift data ahead one day if they punch in before midnight, if after midight it leaves it the same which is what I was looking for.

IIf([shift]=3 And [Time On]>0.75,[date]+1,[date])

I am using this for the "Off time" so if a 2nd shift person who's normal leave time is 11pm leaves after midight it will move thier OFF Date back one day to mach there ON date. It also changes the date for a 3rd shift person if the person transacted on at 11pm and off at 11:30 pm it will move both of those dates ahead one day which is correct.

IIf([shift]=2 And [Time Off]<0.167,[date]-1,IIf([shift]=3 And [Time Off]>0.75,[date]+1,[date]))

Thank you all again for your support on this.

JC