Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query To Fix Time Field? (A2k (9.0.3821) SR-1)

    I have 10,000 records coming from an legacy with an text time field with no am or pm designation

    Valid times are 8:00 am to 7:00 pm, stored as 8:00 to 7:00

    After the file conversion of the time field into a access date field (date/time medium)I have times 1:00 am through 7:00 am that should relally be 1:00 pm through 7: pm

    I need an update query to isolate the records (times 1:00 am through 7:00 am) in question and change the am

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query To Fix Time Field? (A2k (9.0.3821) SR-1)

    Set <#8:00 AM# as criteria, and change the value to [FieldName]+0.5 where FieldName should be replaced by the name of the time field. Since Access counts dates and times in days, adding 0.5 is equivalent to adding a half day = 12 hours.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query To Fix Time Field? (A2k (9.0.3821) SR-1)

    Hi Hans

    +0.5 added and got 5 minutes, I changed it to +12.0 and got my half day

    [dtm1stInspTime]+0.5
    Grid [dtm1stInspTime]+#12:05:00 AM#
    SQL [dtm1stInspTime]+#12/30/1899 0:5:0#
    Added 5 minutes to each time

    [dtm1stInspTime]+12.0
    Grid [dtm1stInspTime]+#12:00:00 PM#
    SQL [dtm1stInspTime]+#12/30/1899 12:0:0#
    Added 12 hours to each time

    Thanks for your help

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query To Fix Time Field? (A2k (9.0.3821) SR-1)

    John,

    Glad you were able to sort it out. This may be due to a difference between Access 97 (which I use) and Access 2000. If I enter [dtm1stInspTime]+0.5 in the query grid in Access 97, it doesn't attempt to convert 0.5 into a date/time format, and it adds half a day = 12 hours. Apparently, Access 2000 tries to outsmart you and assumes that you intended to enter 0:5, i.e. 0 hours 5 minutes.

Posting Permissions

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