date & time question

[dhess62]

the field that I am pulling data from diaplays START_TIME as 3/21/2009
2:00:45AM, the other filed END_TIME shows 3/21/2009 2:05:19AM. Access is
combining the date and times, I need these separated somehow
When workiing in Query Design, how can I separate out the date from the time.
I need the query to report events from todays date only.

Right now when I run the query it gives me info clear back from 3/21/2009

 

[golfinray]

Try: Between 4/20/2009 and Now()

 

[BruceM]

You can format to show either the date or the time. For instance:

TimeOnly: Format([Start_Time],"hh:nn:ss")

DateOnly: Format([Start_Time],"Short Date")

To limit events to today's only, try setting the criteria for Start_Time
and/or End_Time to:

Format([Start_Time],"Short Date") = Format(Date() -1,"Short Date")

or maybe this, depending on your Windows time settings (regional settings in
Control Panel):

Format([Start_Time],"Short Date") = Date()

There may be a cleaner way of doing that, but I can't think what it is.

 

[ghetto_banjo]

in query design you can use the Format function to separate them.

Format([DateTimeVar], "Short Date")

Format([DateTimeVar], "Short Time")

or something similar depending on how you want them formatted.

 

[Hans Up]

the field that I am pulling data from diaplays START_TIME as 3/21/2009
2:00:45AM, the other filed END_TIME shows 3/21/2009 2:05:19AM. Access is
combining the date and times, I need these separated somehow
When workiing in Query Design, how can I separate out the date from the time.
I need the query to report events from todays date only.

I'm not sure I understand what you're looking for, but suspect you
might find the VBA DateValue and TimeValue functions useful.

For example, the Now() function currently returns "4/22/2009 9:55:14 AM"
on my system.

I could extract just the date "piece" with DateValue(Now()), or just the
time component with TimeValue(Now()).

Either way, the return value is Date/Time data type ... just limited to
whichever component you ask for.

Are either of those useful for what you want to accomplish?

 

[BruceM]

That's much better than the Format method I showed. It is the one I tried
to use but couldn't remember the name.

 

[Hans Up]

That's much better than the Format method I showed. It is the one I tried
to use but couldn't remember the name.

Yeah. Reading your earlier message jogged my recollection of DateValue.
That is one I keep forgetting to reach for.

 

[Ken Sheridan]

The TimeValue function doesn't really return the time of day component per
se; what it does is return the time of day on 30 December 1899. You can see
this by entering the following in the debug window:

? Format(TimeValue(VBA.Now),"dd mmmm yyyy hh:nn:ss")

This is because Access implements date/time data as a 64 bit floating point
number with its origin at 30 December 1899 00:00:00 (values before that are
implemented as negative numbers), with the integer part representing the
days, and the fractional part the times of day. Similarly the DateValue
function returns a date/time value at midnight at the start of the day in
question, which you can see in the debug window with:

? Format(DateValue(VBA.Now),"dd mmmm yyyy hh:nn:ss")

For 'time arithmetic' within the same day the date element in the underlying
value returned by the TimeValue function is immaterial of course as, being
zero, it has no effect on the result.

As far as the OP's stated requirement, "I need the query to report events
from todays date only", is concerned, if each 'event' is confined to within
the course of one day then:

WHERE DateValue(START_TIME) = Date

will suffice as the end time is superfluous to the expression (or vice
versa). If on the other hand an event can span more than one day the
expression becomes a little more complex as its necessary to look for rows
where the range includes a period which falls with the current day:

WHERE Date Between DateValue(START_TIME) And DateValue(END_TIME)

Ken Sheridan
Stafford, England