Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2016
    Posts
    1

    Unanswered: VBA SQL Query between two dates

    I am trying to extract some data between two dates from a SQL database. The dates are being taken from two cells in the worksheet where I want the data returned to.

    If I manually put the dates in the query, I get the result I am after, however I can't seem to get it to work using the dates in the Excel worksheet.

    This query string works:
    "select * from tbl_WW_OperatorNotes where DateTime between '2016-04-01' and '2016-04-30' order by DateTime desc"

    This runs but doesn't return any result:
    "select * from tbl_WW_OperatorNotes where DateTime between " & Format(CDate(Range("C3")), "yyyy-mm-dd") & " and " & Format(CDate(Range("C4")), "yyyy-mm-dd") & " order by DateTime desc"
    debug.print shows the string appears correct:
    select * from tbl_PM_OperatorNotes where msgDate between 2016-03-01 And 2016-04-30 Order by msgDate desc

    It seems the only difference between the two query strings is the single quotes around the dates, so I did this instead:
    "select * from tbl_HW_OperatorNotes where DateTime between " & Chr(39) & Format(CDate(Range("C3")), "yyyy-mm-dd") & Chr(39) & " and " & Chr(39) & Format(CDate(Range("C4")), "yyyy-mm-dd") & Chr(39) & " order by DateTime desc"

    and now it works.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Delimit date values using # if ysing JET SQL.
    Code:
    select * from tbl_WW_OperatorNotes
    where DateTime 
    between #" & Format(CDate(Range("C3")), "yyyy-mm-dd") & "# and #' & Format(CDate(Range("C4")), "yyyy-mm-dd") & "# order by DateTime desc
    OR use the appropriate delimiter (or even declare what limiter) for the SQL engine you are using.

    if you dont' delimit the date literal the SQL engine will (proabably) treat the text as a sequence of numbers eg 2016-04-21 could be 1991...

    also be aware that by calling a column datetime you may be using a reserved word
    Last edited by healdem; 04-25-16 at 05:17.
    I'd rather be riding on the Tiger 800 or the Norton

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
  •