Filter by Dates in MS Access using a Form/Query

[originalxavier]

Hello,

I have a database with several tables and fields and I am trying to set up a form that will pull data by the "Problem Type" (which currently works) but only show resposes within a certain Date Range that the user will have to enter. I currently have inputboxes that request the dates, and I have these Dim'ed as Strings. But when I try to build on the functional code to add a date range, I get a syntax error for a missing operator in query expression... Here is some code...

    startdate = InputBox("Please enter a start date (yyyy-mm-dd)")
    startdate = Format(startdate, "yyyy-mm-dd")
    enddate = InputBox("Please enter an end date (yyyy-mm-dd)")
    enddate = Format(enddate, "yyyy-mm-dd")


    strSQL = "SELECT Actions.RequestNo..."
 ..."WHERE ((Actions.DateEntered) Between '" & startdate & "' " & _
    "And '" & enddate & "')"

Any help would be appreciated...
Thanks in advance Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rich Cook
----------------------------------------

 

[vb6novice]

Xavier,

If the DateEntered field in the Actions table is a Date type, then the query will only find the correct records if you provide dates as start and end dates. As you have discovered, the InputBox function only returns a string, and the Format function only tells how to display that string on a form or report, but does not change its value. You must convert each input to a date, then use the # character before and after those variable in the SQL statement.

Dim the startdate and enddate variables as Date type, and convert the entries to date type before storing them to the variables as shown below.

startdate = CDate(InputBox("Please enter a start date (yyyy-mm-dd)"))
enddate = CDate(InputBox("Please enter an end date (yyyy-mm-dd)"))

Then modify your SQL statement by changing the single quotes to the # sign.

    strSQL = "SELECT Actions.RequestNo..."
 ..."WHERE ((Actions.DateEntered) Between #" & startdate & "# " & _
    "And #" & enddate & "#)"

 

[originalxavier]

Still getting the same error for some reason.... Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rich Cook
----------------------------------------