Results 1 to 5 of 5
  1. #1
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    Query Expression to Combine Short Date and Medium Time into single Expression

    I have two fields in a Microsoft Access Database called TicketNbrDate (Date/Time - Short Date -mm/dd/yyyy 11/01/2019) and TicketNbrTime (Date/Time – Medium Time 4:00 PM). I have created a query in which I have created two new fields based on the first two field called TicketNbrDateConv (Date/Time – Short Date but in the format yyyymmdd 2091101) and TicketNbrTimeConv (Date/Time – Medium Time but in the military time format 1600.

    I would like to combine the two fields TicketNbrDateConv and TicketNbrTimeConv into a single field which would look like yyyymmdd-xxxxxx. Where the xxxxxx would represent the military time without the colon and with a preceding zero for single digit hours. This would give me a way of sorting the records in date time order.

    How would I create this field?

    There is probably an easier of way of doing this but being an amateur Access person I am not sure how to do this.

    There may be a way to have this information entered into a single field where the person would enter the date in the format mm/dd/yyyy hh:mm AM or PM.

    Any thoughts.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Date/Time field actually stores date or time as a double number. What you see is just formatting.
    Records will sort even if you don't use military time display.

    Combine the two fields with arithmetic: TicketNbrDate + TicketNbrTime

    Format function returns a string value, not a true date/time.
    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.

  3. #3
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Thanks for your response.

    I set up a query to combine the two fields using the arithmetic expression you provided using two different expressions.

    The first expression combined the TicketNbrDate and TicketNbrTime with the hopes of obtaining an answer in the format mm/dd/yyyy XX:XX AM or PM. I obtained that answer but seconds were added to the result.

    The second expression combined the TicketNbrDate converted to YYYYMMDD format and added to TicketNbrTime in military format XXXXX. The second expression gave me the same result as the first expression.

    My question is why are seconds added to the addition result and why is the second expression giving the same result as the first?

    I am attaching a PDF file which shows my addition expressions.

    Thanks for your help.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    The calculated value simply displays Access default structure. Why is inclusion of seconds an issue in query? Do formatting on report.

    If you want 24-hr display then either use Format() function or apply format property (mm/dd/yyyy hh:nn) to calculated field. Hours less than 10 will not show placeholder zero with format property. In my view that is a bug because month and day will show placeholder zero.

    I personally prefer not to do formatting in tables and queries.
    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. #5
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Thanks I will do formatting in a report.

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

Similar Threads

  1. Replies: 5
    Last Post: 08-10-2018, 02:20 PM
  2. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  3. Replies: 9
    Last Post: 03-28-2016, 03:57 PM
  4. Replies: 2
    Last Post: 01-24-2016, 03:20 AM
  5. Replies: 1
    Last Post: 02-16-2013, 09:11 AM

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