Date/Time Field

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

I would like my [Start_Time] field to always pull records
that fall between today at 15:00 and tomorrow at 14:59.
I've tried several variations and can't get it to work
without prompting for the date/time.

Can someone help?
Thanks,
Mary
 
Mary said:
I would like my [Start_Time] field to always pull records
that fall between today at 15:00 and tomorrow at 14:59.
I've tried several variations and can't get it to work
without prompting for the date/time.
Click to expand...
Hi Mary,

There are many ways, but have you tried

[Start_Time]>= Date() + 15/24 AND [Start_Time]<Date() + 39/24


?Format(Date()+ 15/24,"mm/dd/yyyy hh:mm")
04/13/2004 15:00
?Format(Date()+ 39/24,"mm/dd/yyyy hh:mm")
04/14/2004 15:00

Just one way...

Gary Walter
 
Hi Mary,

My understanding of how Access handles date/time values
is that the integer portion of the value corresponds to
the date, and the fraction component corresponds to the
time as a fraction of 24 hours (so 15 hours would
be .625).

So, in your case, you can use Date() to return the date
value of the current day, and add time to it to set your
criteria. There are two ways that you can add the time,
one is to just calculate the percentage of a day that you
want to add and add that directly as a fraction, the
other is to use the DateAdd() function to add any time
interval.

So, using the first method, I believe that your criteria
would be:
= (Date() + .625) AND < (Date() + 1.625)
Click to expand...

Similarly, you should be able to use:
= DateAdd("h",15,Date()) AND < DateAdd("h",39,Date())
Click to expand...

Note that the Date() function returns only the date
portion of the current date, no time portion (which
equates to midnight). If you are ever interested in
using the current date and time when setting criteria,
you can use the Now() function.

HTH,

Ted Allen
-----Original Message-----
I would like my [Start_Time] field to always pull records
that fall between today at 15:00 and tomorrow at 14:59.
I've tried several variations and can't get it to work
without prompting for the date/time.

Can someone help?
Thanks,
Mary
.
Click to expand...
 
Thank you! That's exactly what I needed.
Mary
-----Original Message-----

Mary said:
I would like my [Start_Time] field to always pull records
that fall between today at 15:00 and tomorrow at 14:59.
I've tried several variations and can't get it to work
without prompting for the date/time.
Click to expand...
Hi Mary,

There are many ways, but have you tried

[Start_Time]>= Date() + 15/24 AND [Start_Time]<Date() + 39/24


?Format(Date()+ 15/24,"mm/dd/yyyy hh:mm")
04/13/2004 15:00
?Format(Date()+ 39/24,"mm/dd/yyyy hh:mm")
04/14/2004 15:00

Just one way...

Gary Walter


.
Click to expand...
 
I have kind of a similar problem with a query. I'm trying to find all
EventTimes logged during a Third shift (22:30 to 06:00). In my query I'm
referring controls on a form with shift times. I realize the shift spanning
Midnight complicates things.
Any suggestions appreciated.
Sid
Ted Allen said:
Hi Mary,

My understanding of how Access handles date/time values
is that the integer portion of the value corresponds to
the date, and the fraction component corresponds to the
time as a fraction of 24 hours (so 15 hours would
be .625).

So, in your case, you can use Date() to return the date
value of the current day, and add time to it to set your
criteria. There are two ways that you can add the time,
one is to just calculate the percentage of a day that you
want to add and add that directly as a fraction, the
other is to use the DateAdd() function to add any time
interval.

So, using the first method, I believe that your criteria
would be:
= (Date() + .625) AND < (Date() + 1.625)
Click to expand...

Similarly, you should be able to use:
= DateAdd("h",15,Date()) AND < DateAdd("h",39,Date())
Click to expand...

Note that the Date() function returns only the date
portion of the current date, no time portion (which
equates to midnight). If you are ever interested in
using the current date and time when setting criteria,
you can use the Now() function.

HTH,

Ted Allen
-----Original Message-----
I would like my [Start_Time] field to always pull records
that fall between today at 15:00 and tomorrow at 14:59.
I've tried several variations and can't get it to work
without prompting for the date/time.

Can someone help?
Thanks,
Mary
.
Click to expand...
Click to expand...
 
Back
Top