Results 1 to 4 of 4
  1. #1
    ldashev is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2016
    Posts
    3

    Query results end date cuts off the last day

    Odd thing happened today - working on a regular query that is sitting on a 2015-2016 table. Query normally brings back all "NEW" alerts for a given month and this month I realized that it was not including the last 19 records since the dates time stamps were on 11/30/2016 but after midnight. This was the first time that I came across this as those dates are never on the last day of the month.



    Query is set up with a clause: Between [Enter start date in date format 'mm/dd/yyyy':] And [Enter end date in date format 'mm/dd/yyyy':] and the input dates for this month were 11/1/2016 to 11/30/2016.

    Has anyone dealt with something similar and knows a quick solution to change the query to recognize the records with timestamps for 11/30/2016? (illustration below)

    P.S. I also tested the query by manually changing the time to 11/30/2016 00:00:00


    ORIGINAL_ACTION_TYPE ALERT_CREATION_TIMESTAMP ALERT_SCORE SCENARIO_ABBREVIATION FOCAL_ENTITY_TYPE
    New 11/15/2016 17:34 875 User ACCOUNT
    New 11/30/2016 15:36 875 User ACCOUNT
    New 11/30/2016 15:40 875 User ACCOUNT
    New 11/30/2016 15:42 875 User ACCOUNT
    New 11/30/2016 15:44 875 User ACCOUNT
    New 11/30/2016 15:45 875 User ACCOUNT
    New 11/30/2016 15:46 875 User ACCOUNT
    New 11/30/2016 15:47 875 User ACCOUNT
    New 11/30/2016 15:48 875 User ACCOUNT
    New 11/30/2016 15:49 875 User ACCOUNT
    New 11/30/2016 15:50 875 User ACCOUNT
    New 11/30/2016 15:50 875 User ACCOUNT
    New 11/30/2016 15:52 875 User ACCOUNT
    New 11/30/2016 15:53 875 User ACCOUNT
    New 11/30/2016 15:54 875 User ACCOUNT
    New 11/30/2016 15:55 875 User ACCOUNT
    New 11/30/2016 15:56 875 User ACCOUNT
    New 11/30/2016 15:57 875 User ACCOUNT
    New 11/30/2016 15:58 875 User ACCOUNT
    New 11/30/2016 15:59 875 User ACCOUNT

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    >= [Enter start date in date format 'mm/dd/yyyy':] And < CDate([Enter end date in date format 'mm/dd/yyyy':] +1)

  3. #3
    ldashev is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2016
    Posts
    3
    Tested it and worked perfectly! Thank you!

    Quote Originally Posted by aytee111 View Post
    >= [Enter start date in date format 'mm/dd/yyyy':] And < CDate([Enter end date in date format 'mm/dd/yyyy':] +1)

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Just to add a bit of info to this thread: All DateTime fields have a Timer component. It is often set to 0 which equates to Midnight (12:00 AM) which is the First hour of the day. Here's a link to MS explaination: https://support.microsoft.com/en-us/kb/130514

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

Similar Threads

  1. Exporting in .rtf cuts off the end of the text field
    By louise in forum Import/Export Data
    Replies: 1
    Last Post: 12-10-2015, 04:00 PM
  2. Replies: 3
    Last Post: 07-31-2015, 11:40 AM
  3. Getting correct date from query results
    By riggsdp in forum Programming
    Replies: 5
    Last Post: 11-11-2014, 04:01 PM
  4. Query Results on an 'as at' date
    By roarcrm in forum Queries
    Replies: 3
    Last Post: 09-18-2014, 03:33 AM
  5. Replies: 3
    Last Post: 07-13-2012, 09:18 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