Jerry:
Is the problem with importing the data into access or with processing it
once imported?
The key thing about date/time values in Access is that they are all
precisely that; you can't have time value without a date, and you can't have
a date without a time of day. If a time is entered without any date the
value stored by Access represents that time on 30 December 1899, and a date
entered without a time of day represents midnight at the start of the day.
This is because a date/time value is implemented as a 64 bit floating point
number as an offset from 30 December 1899 00:00:00. The integer part
represents the days, the fractional part the times of day.
The formatting of date/time values is completely independent of the data
itself. By default the local regional short date format as set in the
Windows control panel is used, but the values can be formatted however you
wish.
To return the difference in minutes between two date/time values the
DateDiff function is used, specifying the interval argument as "n" ("m" is
used for months). If the values are all within the same day this will return
the correct result if the times have been entered without dates as it is in
effect subtracting values on 30 December 1899, but the date is irrelevant.
If the values are in different days, however, the range spanning midnight,
the values will need to include the actual dates. Its best if the date and
time are stored in a single field, but if they are stored in separate field
the two fields' values can simply be added together to give the full
date/time value.
To group the data within ranges per day you can use the TimeValue function
to extract the time from a date (in reality its giving you the time on 30
December 1899 of course, but the date is immaterial in this context). So for
start times before 7:00 AM:
TimeValue([StartTime]) < #07:00:00#
For end times before 7:00 PM:
TimeValue([EndTime]) < #19:00:00#
For cases starting and ending between 7:00 AM and 7:00 PM:
TimeValue([StartTime) >= #07:00:00# And TimeValue([EndTime]) <= #19:00:00#
or whatever other combinations of times you wish.
Formatting should only be used for controlling how the data is presented.
For processing it work with the raw date/time values and delimit literal
date/time values with the # character. As with data in a table, if you omit
the date or time from a literal value, as with #07:00:00# etc above, this
will be interpreted as the time on 30 December 1899 (in the absence of a
date) or midnight at the start of the day (in the absence of a time).
Ken Sheridan
Stafford, England
