Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    ICBSprod's Avatar
    ICBSprod is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Norway
    Posts
    10

    Thumbs up

    Hi!

    Thanks for all help, discussions and points of view.

    Based on this, I ended up with the following solution:

    Table: 4 Date/time fields.

    1: Start date
    2: Start time
    3: End date
    4: End time

    Query:
    In addition to the 4 above, and my old fields I added 3 new fields.
    1: Start date and Start time - merged
    2: End date and End time - merged
    Now I have both date and time accessible for calculation
    3: Elapsed time with the following code: =24*([End Time]-[Start Time]), based on the merged fields.

    Form:
    Start date and End date have no tabs, Date now is set as standard value, and I have set an input mask.


    This means I don't have to bother about punching dates, in 99.99% of the occations. But I can still access the date fields, and punch in the actual date/s with just four digits each, if necessary.
    Starting a new record, tabs takes me directly to Start time, then to End time. With an input mask here too; Punch 4 digits - tab - and 4 digits again, and I am done!!
    The calculations are made directly, with no fuzz, and it handles passing midnight.

    Thanks to all of You, and I wish You all a Merry Christmas!

    Børge - Norway

  2. #17
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Good to see you are now storing date & time in the same fields. However a few comments:
    1, Avoid spaces in field names. Better to use StartDateTime, EndDateTime or similar as field names
    2. The original fields should no longer exist. Delete all of them (if not already done) to avoid duplication
    3. Elapsed Time should be calculated in a query - it should not be saved as a separate field

    A basic rule in database design is that data should be saved ONCE and derived data calculated using queries.
    There are exceptions to the rule but this isn't one of them
    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

  3. #18
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Good to see you are now storing date & time in the same fields.
    Maybe the post was edited after you wrote that? Looks like 4 fields instead of 2.
    Table: 4 Date/time fields.

    1: Start date
    2: Start time
    3: End date
    4: End time
    I think that's part of the problem...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't think so.

    In addition to the 4 above, and my old fields I added 3 new fields.
    1: Start date and Start time - merged
    2: End date and End time - merged
    Now I have both date and time accessible for calculation
    3: Elapsed time with the following code: =24*([End Time]-[Start Time]), based on the merged fields.


    Possibly these are all calculated fields.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No idea whether it was edited by the OP but if not I seem to have misread the OP's last reply.
    My last answer stated what your table should have. Just two fields each containing date and time.
    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

  6. #21
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm going to back out because it's too confusing. What June7 posted is about a query, not a table. You and I seem to be advocating a table whose date/time fields include time, not separating the components.
    Ciao.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Convert Number to time format
    By Nalaka in forum Access
    Replies: 7
    Last Post: 01-14-2019, 04:42 AM
  2. Replies: 1
    Last Post: 08-12-2014, 09:48 PM
  3. Replies: 5
    Last Post: 05-14-2014, 03:19 PM
  4. Convert Number to Date/Time
    By Jerseynjphillypa in forum Queries
    Replies: 10
    Last Post: 06-13-2012, 12:33 PM
  5. Replies: 1
    Last Post: 08-07-2011, 07:58 AM

Tags for this Thread

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