Results 1 to 9 of 9
  1. #1
    XXXvelo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    4

    Access Automaticaly appending current date (time report was run) to Time field

    Hello all. I am in the process of converting all of our queries at my job from an old Access DB (97) to Access 2016. Even though this query is very simple, combining employee punches with employee name, there is a difference in the data I get between old and new. This is what is happening:



    --------------------Clock In---------------------Clock Out

    Access 97 --------05:30:36 AM ---------------03:27:25 PM

    Access 2016 -----07/17/19 05:30:36 AM ----07/17/19 03:27:25 PM

    For some reason, on the TIME fields, Access is putting in the date the report was run in front of it. So yesterday it was 07/16/19 even though the date range I am running the data was the 15th on both days. Having looked at the table format, it is Date/Time, but the source data (our manufacturing ERP system) does not have a date inside of a time field. I have searched extensively online and couldn't find anything. I would like to know how I can get rid of the date and just have the time as a result in the query export. I am using design view btw. I have little experience with SQL but am willing to try if thats the only way to solve this. I am also trying to avoid formatting after exporting to Excel.

    Please help!!

    Edit: Sorry for the dashes in the little table I made, all the spaces I put in didn't stick when it was posted.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Having looked at the table format, it is Date/Time
    Date/Time is the field type, not the format

    dates are stored as numbers - you format them as you require - show/not show the date, show/not show the time, format date as dd/mm/yyyy or dd mmm yy, whatever

    now for example is 43663.5402893519

    43663 represents the day (number of days since 31/12/1899)

    .5402893519 is the time expressed as the number of seconds to now divided by 86400 (the number of seconds in a day)

    so suspect this is a presentational issue if you are looking at the data

    suggest you look at all the date functions and use them to compare data between the two systems - see about 20% down this link https://support.office.com/en-ie/art...2-658ce330ed83

  3. #3
    XXXvelo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    4
    Thanks for the response. You pointed me in a good direction. I looked at how that table is formatted in Access 97 and those fields in question are Text. The fields in Access 2016 are Date/Time. I believe that's the issue. So instead of just the time as an expression of seconds as you showed, it's forced to append a date and time.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it's forced to append a date and time.
    access isn't 'forced', it just does what its told. How are you appending the data? if it only has a time element the underlying value will be 0.5402893519 for example. If you are using a function like now() that does include a date

    depends what you are doing with the data but better to store as date/time if you need to do any sort of calculation (such as time between start and end or find the next start after previous end)

  5. #5
    XXXvelo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    4
    That is the puzzling part of this. I don't see anywhere, whether it be in the query itself, the property sheet, the table properties, or the table design view, where it is formatted or instructed to pull in a date value in front of the time. The only difference I can see is between the old "text" format and the new "Date/Time" format.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without seeing the two versions (97/2016) plus output from your erp system, not really possible for me to say.

    just remember the format function changes a date to text, the format property displays the text but leaves the underlying value unchanged.

  7. #7
    XXXvelo is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    4
    I was finally able to get around this by using a TimeValue expression in the query in those fields that are causing issues. Not perfect but it's something.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by XXXvelo View Post
    I looked at how that table is formatted in Access 97 and those fields in question are Text. The fields in Access 2016 are Date/Time.
    I'm really curious:
    If the A97 "Clock In"/"Clock Out" fields are TEXT and the dB is working, why have those two field types been changed to Date/Time in A2016???
    (If it ain't broke, don't fix it!!)

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Micron explained why a date was being added to the time.
    Datetime fields are stored as double numbers
    If you type this into the immediate window you will see the current value?cdbl(now())
    I got 43664.3548263889 Where 43664 is today and the decimal part is the portion of today that has elapsed.

    In some versions of Access, omitting the date will cause it to be stored as e.g. 0.25 (for 6am)
    The zero date is the base date in Access and shown as 30/12/1899 or 12/30/1899 depending on your location.


    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-06-2018, 01:30 PM
  2. Replies: 2
    Last Post: 01-16-2015, 04:22 PM
  3. Enter current date and time into subform field
    By tonybrecko in forum Forms
    Replies: 8
    Last Post: 06-16-2013, 09:58 PM
  4. Replies: 1
    Last Post: 08-14-2012, 03:22 AM
  5. VB coding to show current date n time in access form
    By cwwaicw311 in forum Programming
    Replies: 6
    Last Post: 02-10-2010, 09:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums