Results 1 to 10 of 10
  1. #1
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169

    Inclusive Between Date Query on input data from user

    I have searched for the answer to this but just can't seem to figure it out. I am trying to do a query using BETWEEN/AND on a date field that contains time. When the user clicks a button, they are asked to put in the start date and end date. So for example if the user wanted records between the dates of 9/25/2010 and 9/30/2010 the records from 9/30/2010 do not return. I know this is a factor of the time and that Access views the date of 9/30/2010 at the time of 00:00:00 so technically the query runs correct since all dates do fall between 9/25/2010 00:00:00 and 9/30/2010 00:00:00. I assume I have to add one day to the day input by the user but can't figure out the syntax. Anybody know how to do this? Thanks.


    Code:
    SELECT tblQuestions.*, tblQuestions.LastUpdated
    FROM tblQuestions INNER JOIN tblTempUniqueQuestIDchanges ON tblQuestions.QuestID = tblTempUniqueQuestIDchanges.QuestID
    WHERE (((tblQuestions.LastUpdated) Between [Enter start date] And [Enter thru date]));

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    Have you looked at the DateAdd() function yet?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    No, not familiar with that...will that accomplish what I need?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    Yes. Have a look in VBA Help.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    OK, I tried this:
    Code:
    WHERE (((tblQuestions.LastUpdated) Between [Enter start date] And ( DateAdd ("d",1, [Enter thru date]))))
    and it only returned one date from the thru date. It was an early morning updated record. So is this adding a full day to the query? It seems to be missing records from later in the day.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    It should be. Are you copying your code and pasting to your post or retyping it in the post. The spacing does not look right for Access.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    I was copying and pasting. But now for some reason when I run the query, it is asking me twice for the start and end dates...something isn't right, obviously. I will keep messing with it.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    I'll stay tuned.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  9. #9
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Ok, after stepping away from this for several hours and closing the DB and starting over, it seems to be working correctly. Thanks RG for steering me in the right direction.
    Code:
    WHERE (((tblQuestions.LastUpdated) Between [Enter start date] And (DateAdd("d",1,[Enter thru date]))))

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    You're certainly welcome. Thanks for posting back with your success.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Reporting with user input
    By tazbergy in forum Reports
    Replies: 1
    Last Post: 09-12-2010, 11:26 PM
  2. Replies: 6
    Last Post: 07-22-2010, 05:53 PM
  3. user selected date query
    By jamin14 in forum Queries
    Replies: 3
    Last Post: 03-31-2010, 02:11 PM
  4. Replies: 0
    Last Post: 03-22-2010, 07:51 PM
  5. User Input Query
    By ManC in forum Queries
    Replies: 2
    Last Post: 03-04-2010, 07:09 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 - Senior Forums