time and date calculation

[plgii via AccessMonster.com]

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?

 

[Douglas J. Steele]

You'd be best off storing the date in apptTime, and getting rid of
pickupDate.

To refer to only the date portion of a date/time value, use the DateValue
function. To refer to only the time portion, use the TimeValue function.

 

[plgii via AccessMonster.com]

pickupDate is chosen by user from popup calendar, so it needs to be there,
but if I understand you, once pickupDate is entered then write date&time to
apptTime? How would this help the conditional formatting?

You'd be best off storing the date in apptTime, and getting rid of
pickupDate.

To refer to only the date portion of a date/time value, use the DateValue
function. To refer to only the time portion, use the TimeValue function.

OK so I have a form with the following fields which are all date/time type
apptTime

[quoted text clipped - 24 lines]

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?

 

[Douglas J. Steele]

You'd compare the combined date/time to Now(), as opposed to comparing the
time-only to Time()

BTW, to combine a date and time into a single field, all you need to do is
add them together.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

pickupDate is chosen by user from popup calendar, so it needs to be there,
but if I understand you, once pickupDate is entered then write date&time
to
apptTime? How would this help the conditional formatting?

You'd be best off storing the date in apptTime, and getting rid of
pickupDate.

To refer to only the date portion of a date/time value, use the DateValue
function. To refer to only the time portion, use the TimeValue function.

OK so I have a form with the following fields which are all date/time
type
apptTime

[quoted text clipped - 24 lines]

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?

 

[plgii via AccessMonster.com]

can you give me a bit of code on it? I have tried so many combinations, my
mind is racked...I would guess you mean in the conditional formatting to use
Now() instead of Time().

You'd compare the combined date/time to Now(), as opposed to comparing the
time-only to Time()

BTW, to combine a date and time into a single field, all you need to do is
add them together.

pickupDate is chosen by user from popup calendar, so it needs to be there,
but if I understand you, once pickupDate is entered then write date&time

[quoted text clipped - 14 lines]

 

[Douglas J. Steele]

Without knowing exactly what you're doing, I can only offer generalities.

In your original post, you mentioned that it works with "timerOn<=Time() and
pickupDate=Date()" until you get to midnight.

Using your existing fields, try:

"DateAdd("h", -2, pickupDate + apptTime) < Now()"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

can you give me a bit of code on it? I have tried so many combinations, my
mind is racked...I would guess you mean in the conditional formatting to
use
Now() instead of Time().

You'd compare the combined date/time to Now(), as opposed to comparing the
time-only to Time()

BTW, to combine a date and time into a single field, all you need to do is
add them together.

pickupDate is chosen by user from popup calendar, so it needs to be
there,
but if I understand you, once pickupDate is entered then write date&time

[quoted text clipped - 14 lines]

system
time to 22:29. Can anybody give me insite on where the logic is wrong?

 

[plgii via AccessMonster.com]

Douglas
Ok that worked to some degree. I actually had to test both as:

"DateAdd("d", -1, pickupDate + apptTime) < Now()" And "DateAdd("h", -2,
pickupDate + apptTime) < Now()"

this tests both and works, but it still ignores the timerOn variable and
since I am allowing user to select up to 8 hours of reminder - in 1 hour
increments, I need to be able to test against another condition since the
DateAdd is hardcoded to -2. I can place the above line for up to the 8 hours
in the Conditional Formatting control. When I tried to use this in onTimer
control for the subform it will work, but if the condition is not true I
can't get backcolor to go back to default white. I used:

If "DateAdd("d", -1, pickupDate + apptTime) < Now()" And "DateAdd("h", -2,
pickupDate + apptTime) < Now()" then
Me!status.backcolor = vbYellow
else
Me!status.backcolor = white
endif

the 1st condition was true, but if I set the system time back a minute or so
before apptTime -2, it should have gone back to white but it does not. Am I
testing it right in vba? and should I not be able to test another condition
such as:

[th]=2 And "DateAdd("d", -1, pickupDate + apptTime) < Now()" And "DateAdd("h",
-2, pickupDate + apptTime) < Now()"
so that I can have a hidden field [th] that is set by the hour increment
user selects and only format when BOTH conditions are true?

Any insight on this?

Without knowing exactly what you're doing, I can only offer generalities.

In your original post, you mentioned that it works with "timerOn<=Time() and
pickupDate=Date()" until you get to midnight.

Using your existing fields, try:

"DateAdd("h", -2, pickupDate + apptTime) < Now()"

can you give me a bit of code on it? I have tried so many combinations, my
mind is racked...I would guess you mean in the conditional formatting to

[quoted text clipped - 13 lines]

 

[Douglas J. Steele]

The quotes around the function calls are incorrect in the If statement, and
you need vbWhite, not strictly White.

If DateAdd("d", -1, pickupDate + apptTime) < Now() And _
DateAdd("h", -2, pickupDate + apptTime) < Now() Then
Me!status.backcolor = vbYellow
Else
Me!status.backcolor = vbWhite
End If

The fact that you're not getting an error with White implies to me that you
haven't told Access to force variable declaration (or else you must have a
variable named White defined somewhere in the routine). In my opinion, you
should ALWAYS have Option Explicit at the beginning of each module. In that
way, you may have caught the fact that White is meaningless to Access. To
force Option Explicit to be added to each routine by default, go into Tools
| Options while you're in the VB Editor, and ensure that "Require Variable
Declaration" is checked on the Editor tab.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas
Ok that worked to some degree. I actually had to test both as:

"DateAdd("d", -1, pickupDate + apptTime) < Now()" And "DateAdd("h", -2,
pickupDate + apptTime) < Now()"

this tests both and works, but it still ignores the timerOn variable and
since I am allowing user to select up to 8 hours of reminder - in 1 hour
increments, I need to be able to test against another condition since the
DateAdd is hardcoded to -2. I can place the above line for up to the 8
hours
in the Conditional Formatting control. When I tried to use this in onTimer
control for the subform it will work, but if the condition is not true I
can't get backcolor to go back to default white. I used:

If "DateAdd("d", -1, pickupDate + apptTime) < Now()" And "DateAdd("h", -2,
pickupDate + apptTime) < Now()" then
Me!status.backcolor = vbYellow
else
Me!status.backcolor = white
endif

the 1st condition was true, but if I set the system time back a minute or
so
before apptTime -2, it should have gone back to white but it does not. Am
I
testing it right in vba? and should I not be able to test another
condition
such as:

[th]=2 And "DateAdd("d", -1, pickupDate + apptTime) < Now()" And
"DateAdd("h",
-2, pickupDate + apptTime) < Now()"
so that I can have a hidden field [th] that is set by the hour increment
user selects and only format when BOTH conditions are true?

Any insight on this?

Without knowing exactly what you're doing, I can only offer generalities.

In your original post, you mentioned that it works with "timerOn<=Time()
and
pickupDate=Date()" until you get to midnight.

Using your existing fields, try:

"DateAdd("h", -2, pickupDate + apptTime) < Now()"

can you give me a bit of code on it? I have tried so many combinations,
my
mind is racked...I would guess you mean in the conditional formatting to

[quoted text clipped - 13 lines]

system
time to 22:29. Can anybody give me insite on where the logic is
wrong?

 

[plgii via AccessMonster.com]

sorry that was a typo error..I did use vbWhite, but testing with the [th]
variable seems to do the trick so now when user selects 5 hour reminder it
checks that - [th]=5 And DateAdd("d", -1, pickupDate + apptTime) < Now() And
DateAdd("h", -5, pickupDate + apptTime) < Now() - are both true ....etc...
for each time up to 8 hours and does the formatting. If the people who want
these features knew what it took to contain the logic, they might not want it.
Thanks for your help

The quotes around the function calls are incorrect in the If statement, and
you need vbWhite, not strictly White.

If DateAdd("d", -1, pickupDate + apptTime) < Now() And _
DateAdd("h", -2, pickupDate + apptTime) < Now() Then
Me!status.backcolor = vbYellow
Else
Me!status.backcolor = vbWhite
End If

The fact that you're not getting an error with White implies to me that you
haven't told Access to force variable declaration (or else you must have a
variable named White defined somewhere in the routine). In my opinion, you
should ALWAYS have Option Explicit at the beginning of each module. In that
way, you may have caught the fact that White is meaningless to Access. To
force Option Explicit to be added to each routine by default, go into Tools
| Options while you're in the VB Editor, and ensure that "Require Variable
Declaration" is checked on the Editor tab.

Douglas
Ok that worked to some degree. I actually had to test both as:

[quoted text clipped - 51 lines]