Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where clause with dates (2003)

    I've forgotten and hope that someone can remind me of the following: I'm reading two text boxes to create a WHERE clause to specify a range of dates. In my code how can I correctly/ reliably construct this clause? I'm currently ending up with 'SELECT * FROM tblSome WHERE SomeDate BETWEEN 01/01/1990 AND 27/03/2007' having used CDate to convert the textbox content to dates. This is not a valid statement. Bearing in mind also that I'm in the UK.. Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where clause with dates (2003)

    Use something like this:

    strSQL = "SELECT * FROM tblSome WHERE SomeDate Between #" & _
    Format(Me.txtStart, "mm/dd/yyyy") & "# And #" & Format(Me.txtEnd, "mm/dd/yyyy") & "#"

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Where clause with dates (2003)

    Here is how I do it.

    "([SomeDate] Between #" & Format(Me.txtStartDate, "mm/dd/yyyy") & "# and #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#)"

    For SQL dates need to be in mm/dd/yyyy format.
    I format the text boxes as dd/mm/yyyy and don't use CDate.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where clause with dates (2003)

    Thank you (both). I'll have to try it in a bit - but this will work consistently even though I'm based in the uk? Andy.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Where clause with dates (2003)

    I am in Australia, and I use dd/mm/yyyy date format as my standard, and this works consistently for me.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where clause with dates (2003)

    Yes, the Format function ensures that the dates are converted to US date format (which is what SQL expects and needs).

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where clause with dates (2003)

    Still struggling with this. I'm using DoCmd.OpenReport someReport,acPreview,,strWhere and can see that strWhere has the value
    DateArrived Between #01/01/1980# And #10/03/2008#
    But it persists in showing dates beyond the 10th March. I think it's treating the date as 3rd October, as I don't seem to get this problem if I use 13/03/2008, 13th March. Any assistance appreciated. Andy.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where clause with dates (2003)

    If the end date is 10 March 2008, Format(..., "mm/dd/yyyy") should convert it to 03/10/2008, the format that SQL needs. Are you sure that you have used the Format function this way?

  9. #9
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where clause with dates (2003)

    Oops. Penny's just dropped. I can format the text boxes as dd/mm/yyyy but need to use mm/dd/yyyy within the SQL statement. Thanks, Andy.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where clause with dates (2003)

    Yep, that's it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •