Results 1 to 3 of 3
  1. #1
    chrisdd is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    13

    SQL search between 2 dates in SQL

    I've got a database that needs to display records between 2 dates.
    At the moment I've got a form with 2 text boxes on - one for datefrom and one for dateto
    My plan was to format and store the values from these text boxes as global variables
    DateFrom = Format(Me.DateFrom, "yyyy/mm/dd") DateTo = Format(Me.DateTo, "yyyy/mm/dd")
    that I cam then pass into a passthrough query with this criteria for field called dateCollected
    HAVING (((Collections.DateCollected) str_to_date(datefrom, ‘%y/%m/%d’) and str_to_date(dateto, ‘%y/%m/%d’)) AND ((dbo.udfRawHazPropsList([ConsignmentNoteNumber],[ewc_code])) Is Not Null))


    However, no matter what I try I can't get this to work. Any ideas as to a solution would be much appreciated!


  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You shouldn't have to format the dates when saving them. As matter as fact, you probably should NOT try to format them while saying them, as the FORMAT function will actually return a Text value, and it will not be treated as a Date unless you convert it back. Access stores dates as whole numbers anyway (and time as decimals), so there is no need to format them to save them.
    See: https://docs.microsoft.com/en-us/off...tive%20numbers.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Also, the HAVING clause seems incomplete. It looks like you're missing the word "Between", like

    HAVING (((Collections.DateCollected) Between str_to_date(...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Search between 2 dates using 2 textboxes
    By Wingnut74 in forum Forms
    Replies: 3
    Last Post: 04-30-2020, 12:55 AM
  2. Search field for Dates
    By Topflite66 in forum Forms
    Replies: 4
    Last Post: 04-10-2020, 08:49 AM
  3. Search by weeks and or months (dates)
    By sdc1234 in forum Access
    Replies: 3
    Last Post: 08-30-2013, 10:57 AM
  4. Search Query - Between Dates
    By AndreasPanayiotou in forum Queries
    Replies: 1
    Last Post: 09-10-2012, 07:06 AM
  5. Search for specific dates
    By natalia in forum Queries
    Replies: 2
    Last Post: 09-27-2010, 03:15 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