Results 1 to 6 of 6
  1. #1
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93

    Format date and time query

    I am importing a list of events from excel into access. My excel data has a column with dates and times, formatted as: Custom: mm/dd/yy hh:mm AM/PM. When imported in access, it is formatted as General Date: mm/dd/yy hh:mm:ss AM/PM.
    I have a query that pull up events with today's date, from the above mentioned field.
    My problem: If does pull up today's date BUT ONLY if the time has occured yet. In other words, if the field reads 2/5/2016 12:00 PM, and I run the query at 8:00 am, that record of data does not show up. If I run the query after 12:00 pm, it does.
    I have the field criteria set up as: Between Date() And Date()-9 (I also want it to pull 9 days prior). I have also tried Between Today() And Date()-9, and Between Date()-.25 And Date()-9.


    Nothing works.
    I as suspecting it has to do with how it was formatted in excel. But, I really don't want to do my export all over again.
    Any help is greatly appreciated.
    Thanks!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Actually a DateTime field is really a special type of Number field. Why not add an extra day to the first date so it will go catch anything up to midnight? Between Date()+1 And Date()-9

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's a link about how Access store a DateTime value: https://support.microsoft.com/en-us/kb/210276

  4. #4
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    I don't want to go ahead a full day, but would rather go ahead 12 hours. So, I put in "Between Date()+0.5 And Date()-9" to try to go ahead 12 hours. After saving the query and going back, it now reads "Between Date()+#12:05:00 AM# And Date()-9". So, still not sure how to make it go ahead 12 hours? Very confused...

  5. #5
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    Stated another way:
    My query generates a call list, based upon the start date of an event. Someone from our office calls a rep at each event that starts today, and runs for up to none days.
    The problem is, the start date field also includes a start time.
    So, what happens is if the start date is today (Jan. 8, 2016), and the event starts at 12:00 pm, if the report is run at 8:00 am, then that record does not show on the report, because it is not 12:00 pm yet. So, that event rep is not called, even though they should be, because their event starts today.
    I guess another way would be to round the date/time to the nearest date, but I can’t figure out how to do that either.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The DateAdd() function can add days, minutes, hours...pretty much whatever you want.

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

Similar Threads

  1. Format date and time query
    By wolfm in forum Queries
    Replies: 2
    Last Post: 10-26-2015, 08:34 AM
  2. Replies: 3
    Last Post: 08-20-2014, 01:47 PM
  3. Form Check Date/Time Format
    By JayRab in forum Forms
    Replies: 7
    Last Post: 02-23-2014, 09:24 PM
  4. Replies: 5
    Last Post: 06-23-2012, 04:30 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