time and date question

[Eugene]

Greetings all,

I am trying to write a routine that will look at the time and if it greater
then 4:00:00 PM will set the date to the next day and time to 08:00:00. In
psuedocode.....

If Timercvd > 04:00:00 PM and < 11:59:59 PM then

Datercvd = date()+1
Timercvd = 08:00:00 AM

Endif

What is the correct way to code this?

Thanks in advance,

Eugene

 

[John Spencer]

-- Time and date literals are delimited with # signs, just like text
strings are delimited with quotes.

-- If statements contain tests that return true or false and each test
should (normally) consist of three parts.
A value, a comparison operator, and another value

If Timercvd > #04:00:00 PM# and Timercvd < #11:59:59 PM# then

Datercvd = date()+1
Timercvd = #08:00:00 AM#

End If

Normally, I would include BOTH the date and the time in one field. You
can always separate out the date or time portion with DateValue and
TimeValue functions.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jim Burke in Novi]

Not that it's any big deal, but if you're only looking at a time (no date
included) and you only care that the time is after 4:00, then you don't have
to check if it's < 23:59 - you only need to check a range of times if the
upper limit is < midnight. If the time was included with a date value that's
a different story. So all you really need is

If Timercvd > #04:00:00 PM# then

 

[Dale Fye]

Eugene

It depends on where this is, and what it is.

Is TimeRcvd a field in a table or bound to a control on a form, or is it an
unbound control.

If it is a field, and you want to make this change for all records in your
database, then you could do it in a query. Generally, I try to store time
and date values in a single field as #3/4/2009 15:00:00#. I can then
manipulate this single value using the DateValue and TimeValue functions.
So, if it were me, I would probably have two unbound textboxes on a form
(txt_DateRcvd and txt_TimeRcvd), I would then have a bound and hidden textbox
(txt_DTRcvd) on the form where I concatenate the Date and Time values.

Furthermore, I would hesitate to actually change the Date/Time received in
my database, because I like to keep my data accurate. What I would do is use
a query to transform the DTRcvd value when I get ready to use it. You could
either do this with an IIF statement or with a user defined function. A
query with an IIF transformation might look like:

SELECT ID, DTRcvd,
DateValue(DTRcvd) + iif(TimeValue(DTRcvd) < #16:00#, 0, 1) as DateRcvd,
iif(TimeValue(DTRcvd) < #16:00#, TimeValue(DTRcvd), #08:00#) as TimeRcvd
FROM yourTable


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Eugene]

Dale, Jim and John:

Thank you all for your input on this. I got some excellent suggestions and
valuble knowledge.

Eugene