Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Formatting Time in Access

Discussion in 'Information Technology' started by Shemot3467, Jul 28, 2006.

  1. Shemot3467

    Shemot3467
    Expand Collapse
    Guest

    I work in the Anesthesia Finance Office and we are having to calculate the
    total number of minutes a case lasts in the OR by subtracting the end time
    from the start time. We also need to be able to sort the cases in various
    ways such as Start time before 7:00 a.m. and end time before 7:00 p.m. and
    everything in between. The information is extrapolated out of the IDX
    billing system and downloaded as a text file. We import the file into Access
    but have never been able to get the Date/Time formatting to work like we need
    it. HELP !!!!

    Jerry
     
  2. Loading...


  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    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

    "Shemot3467" wrote:

    > I work in the Anesthesia Finance Office and we are having to calculate the
    > total number of minutes a case lasts in the OR by subtracting the end time
    > from the start time. We also need to be able to sort the cases in various
    > ways such as Start time before 7:00 a.m. and end time before 7:00 p.m. and
    > everything in between. The information is extrapolated out of the IDX
    > billing system and downloaded as a text file. We import the file into Access
    > but have never been able to get the Date/Time formatting to work like we need
    > it. HELP !!!!
    >
    > Jerry
     

Share This Page