Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Field (Access 2000/2002)

    Heres a wierd one. I have a databse that I have in Access 2000 and one in 2002 I have a Time field in the database that is set to medium time. I enter time either in military time or regular time with the AM or PM and some of the records in the 2000 database if I click in the field in datasheet view I see the 5:00:00 PM and some just show 5:00 PM. In the 2002 database it will show 12/30/1999 5:00:00 PM or just 5:00 PM
    Can someone please help me figure out what is causing this because I am trying to sort a report by time and becuase of the differences in the time fields in different records the sort is not correct?

    Any help I can get is greatly appreciated because I am at my wits end on this one and you guys in Woddy's Lounge are the best........

    Thanks.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Time Field (Access 2000/2002)

    This is not wierd. All date/time fields are stored as a double datatype. The date is stored to the left of the decimal, the time to the right. Formatting determines how you see this information. For example, if you entered 6:00PM, the system stores this as 0.75; and if you display this with a "short date" format, you will only see 12/31/99! Sorting should not be affected by this, unless you are sorting the formatted result, and not the field itself.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Time Field (Access 2000/2002)

    If you are trying to sort report by time only (disregarding date information) you might try using the TimeValue function in your report or underlying query to convert Date/Time field value to a time-only value. For example, if you have a Date/Time field named "DATE_UPDTD", use this expression in a query:
    <pre>TIME_UPDTD: TimeValue([DATE_UPDTD])</pre>

    Then sort on the TIME_UPDTD column to sort records from earliest time to latest, ignoring the date values. Note in this example if the date/time field is null, the expression will generate an error, so you would have to either filter out the null date/time records if there are any, or modify the expression.

  4. #4
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Field (Access 2000/2002)

    In the database I have an appointment date filed showing long date. and a time field showing medium time. I am running a query that I want sorted first by appointment date then by time and some of the fields that have the ime entered in them have this extra date in the field and some do not for some reason so the sort on time is not working. I am lost.
    Thanks.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Time Field (Access 2000/2002)

    The only way around this is to correct those time fields to eliminate the date component. That is, I'm assuming you have separate fields for date and time, and the date component you are seeing in the time field is superfluous. A simple update query would do the trick, where you update your TimeField with this equation: TimeField - Int(TimeField)

    Of course, backup your database before doing this.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Field (Access 2000/2002)

    Thank you all as usual I get the answers I always need here in the lounge. Thanks again

  7. #7
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Field (Access 2000/2002)

    Thank you all as usual I get the answers I always need here in the lounge. Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •