Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Chuckd is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14

    Query based on Start/End Dates


    Good Morning,
    Again, you got the green guy asking for advise....

    I have created a query date I think is simple and uses my contact table and in particular the date Created field which simply picks up the system date and time that the record was created.
    The field is formatted for date, Short Date to be specific. And,
    the criteria for the column contains the following: >=[Enter Report Start Date] And <[End Date?] (Start Date Entered: 3/1/2021 End Date: 4/1/2021)
    I should note that as long as I enter 4/2/2021 as the end date, then it will pick it up.

    The issue is as follows:
    I have reports in the table from 3/1/2021 through 4/1/2021 (today and which were created today)
    The query will give me every report created between 3/1/2021 and 3/31/2021 but will not display the reports created today (4/1/2021) - Although the 4/1/reports are in the table being queried.

    Anyone have any ideas?
    As always any help will be greatly appreciated...

    Thank you all... for any help...

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try:
    >=[Enter Report Start Date] And <[End Date?] +1

    or

    Between Format([Enter Report Start Date],"yyyy\,mm\,dd") And DateAdd("d",1,Format([End Date?],"yyyy/mm/dd"))
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Do you have a time element in the field?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Chuckd is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Howdy,
    I have formatted the field to Short Date so I don't think that I have a time element.
    However the honest truth is I don't know wether I do or do not..

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a form with 2 text boxes; txtStartDate, txtEndDate
    query: select * from table where [DateFld] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

    then the user wont have to keep entering dates, and they can pick from calendar in the textbox.

  6. #6
    Chuckd is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Thanks - Great Idea

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So <= EndDate would work?

    I tend to use the BETWEEN keyword.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Chuckd is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Bob Fitz suggested: Between Format([Enter Report Start Date],"yyyy\,mm\,dd") And DateAdd("d",1,Format([End Date?],"yyyy/mm/dd"))
    I don't know why, but
    <= EndDate would work but only if I dated the end date to be one day later than I wanted

    But it seems to be working now.. also got a suggestion from ranman256 where he suggested I create a form with textbox date pickers.. Haven't worked on that yet but seem not bad

    Again thanks for all your help

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Chuckd View Post
    Bob Fitz suggested: Between Format([Enter Report Start Date],"yyyy\,mm\,dd") And DateAdd("d",1,Format([End Date?],"yyyy/mm/dd"))
    I don't know why, but
    <= EndDate would work but only if I dated the end date to be one day later than I wanted

    But it seems to be working now.. also got a suggestion from ranman256 where he suggested I create a form with textbox date pickers.. Haven't worked on that yet but seem not bad

    Again thanks for all your help
    That leads me to think you do have time in your field?, that is why +1 will work? It depends on what you are storing in that field, not a format issue?
    You need to be aware anyway.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Chuckd is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Thanks for the info... I'll take a look at it and try to get a better understanding on it - It can only help.

  11. #11
    Chuckd is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    So forgive my ignorance, but I am still very green when it comes to this.
    I want to make sure that I understand this.

    1. Where would the statement go?
    2. The name of the Table being used is contacts, the field is called "contact_date", the form is ContactsQuerybyDate and the txtboxes are txtstartdate and txtenddate - both of these are Unbounded.

    Issue:
    What I am having problems understanding is:
    Where the statement is placed
    Statement format. Should it look like this? query: select * from contacts where [contact_date] between forms!ContactsQuerybyDate!txtstartdate and forms!ContactsQuerybyDate!txtenddate

    Sorry if the question is not to clear, but I am a bit confused...

    Thanks

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Between with dates with time won't work regardless of whether you use a date picker or not. I think you should determine whether they contain time or not. Either copy your table and play with the copy, or in the current table, go in to design view and format the date field so that it shows time. If you get something other than 00:00:00 then you are storing time. To use a date picker, you'll have to train users to pick the next day after the one they want. Or you're going to have to use a function in an expression to either add the next day from the day chosen, or add enough hours or minutes to the chosen day to include records up to midnight. That depends on what you want. When I had to deal with this, I added up to one second before midnight of the next day because it was a 24 hour operation and didn't want anything after that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Chuckd is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Micron,
    So, the answer is that I do definitely have time in there. As I said I'm greener, than green and I just took the default that was offered.
    But I must say that I am definitely learning something every day. :-)

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Let us know if you remain stuck or need clarification on the issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    Chuckd is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    14
    Hey Micron,
    First of all, thanks for the help… Now I am stuck so I am going to explain what it is that I would like to make happen.

    1. I would like to create a form where I could enter the date (via calendar) then
    2. The form would set the parameters for the query and
    3. Which in turn would generate the report.

    So basically, we have a form that allows me to select the report I want, enter the startdate and enddate for the report which then spits out the report. And oh yeah, my date fields do have time in them.
    I just can’t seem to get there.
    Thanks for any help you can give me.

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

Similar Threads

  1. Replies: 10
    Last Post: 03-05-2021, 08:24 PM
  2. Replies: 3
    Last Post: 05-10-2018, 11:15 AM
  3. Replies: 5
    Last Post: 10-02-2017, 02:02 PM
  4. List Dates between Start and Finish Dates
    By Buddus in forum Queries
    Replies: 1
    Last Post: 07-26-2013, 01:58 PM
  5. Replies: 1
    Last Post: 12-07-2012, 10:02 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