OK so I have a form with the following fields which are all date/time type
apptTime
timerOn
pickupDate
reminder - combo box with 1 hour, 2 hour...etc up to 10 hours
so what happens is when user enters apptTime say 18:30 with pickupDate set for current days date 10/14/2006 then chooses 2 hours from the reminder combo. This then sets timerOn to (apptTime-2 hours) so it is 16:30. I have set a conditional formatting to change background color when "timerOn<=Time() and pickupDate=Date()" so user knows to dispatch this load. This all works great until you get to midnight.
If user sets apptTime to 00:30 with pickupDate to 10/15/2006 and chooses 2 hour reminder, so timerOn does get set to 22:30 and you would expect the conditional formatting to work once the current Time() is 22:30, but since the current Date() is 10/14/2006, the expression is false. I have tried the DateAdd such as DateAdd("d", 1, Date()) command in various ways but can't get it to work for "timerOn<=Time() and pickupDate=DateAdd("d", 1, Date())"...I mean it works because it adds 1 day to pickupDate and condition is then true, but it ignores the timerOn<=Time() part and formats even if I reset system time to 22:29. Can anybody give me insite on where the logic is wrong?
apptTime
timerOn
pickupDate
reminder - combo box with 1 hour, 2 hour...etc up to 10 hours
so what happens is when user enters apptTime say 18:30 with pickupDate set for current days date 10/14/2006 then chooses 2 hours from the reminder combo. This then sets timerOn to (apptTime-2 hours) so it is 16:30. I have set a conditional formatting to change background color when "timerOn<=Time() and pickupDate=Date()" so user knows to dispatch this load. This all works great until you get to midnight.
If user sets apptTime to 00:30 with pickupDate to 10/15/2006 and chooses 2 hour reminder, so timerOn does get set to 22:30 and you would expect the conditional formatting to work once the current Time() is 22:30, but since the current Date() is 10/14/2006, the expression is false. I have tried the DateAdd such as DateAdd("d", 1, Date()) command in various ways but can't get it to work for "timerOn<=Time() and pickupDate=DateAdd("d", 1, Date())"...I mean it works because it adds 1 day to pickupDate and condition is then true, but it ignores the timerOn<=Time() part and formats even if I reset system time to 22:29. Can anybody give me insite on where the logic is wrong?
