Date/Time Field

[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

 

[Gary Walter]

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.

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

 

[Ted Allen]

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)

Similarly, you should be able to use:

= DateAdd("h",15,Date()) AND < DateAdd("h",39,Date())

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
.

 

[Mary]

Thank you! That's exactly what I needed.
Mary

-----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.

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


.

 

[Sid]

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

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)

Similarly, you should be able to use:

= DateAdd("h",15,Date()) AND < DateAdd("h",39,Date())

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
.