Petay87 Petay87 - 1 year ago 53
SQL Question

Access VBA not working to find entries in database between a date range


I have a form that have multiple button on the page. Each button is used to create a html report so that the data can be easily emailed to other people. I have managed to get 2 button working fine and am about 90% the way to getting the other 2 works as they use the same code, just pass different variables in to the sub.


One of the buttons is to select all records from the "Questions" table where the "CheckDate" field is between 2 dates selected on the form. This is where I am falling down and don't seem to be able to get any search results at all. I have tried using plain text as a search string so that I know the statement is correct but this also doesn't work.


Table = Questions

Fields (Type):

  • ID (AutoNumber)

  • NameID (Number)

  • CheckDate (Date/Time)

  • Problem (Number)

  • LogScreen (Yes/No)

  • ActionPlan (Yes/No)

  • Owner (Yes/No)

  • DueDate (Yes/No)

  • LatestStatus (Yes/No)

  • KE (Yes/No)

  • Status (Yes/No)

  • Links (Yes/No)

  • RootCause (Yes/No)

  • Monitoring (Yes/No)


Below are the relative sections of my code (I haven't put it all in as there is a lot that is not relative):

strSQL = "SELECT * FROM Questions WHERE CheckDate BETWEEN #" & strFrom & "# AND #" & strTo & "#"
Set rstQuestions = dbsQualityCheck.OpenRecordset(strSQL, dbOpenDynaset)

If Not (rstQuestions.EOF And rstQuestions.BOF) Then
Do Until rstQuestions.EOF = True
MsgBox "There are no checks against this Analyst"
End If


The issue here is that I always end up with the Message Box advising that there are no checks against the analyst. I can only assume that the data is not correct or the date is not formatted correct on one side of the check but I am unable to see where the fault lies.

I have also tried the following as the strSQL string:

strSQL = "SELECT * FROM Questions WHERE CheckDate BETWEEN #29/11/2015# AND #01/12/2015#"


try to format your criteria as

 CheckDate BETWEEN #" 
& format(strFrom,"yyyy/mm/dd") & 
"# AND #"
 & format(strTo,"yyyy/mm/dd") & "#"