Petay87 Petay87 - 5 months ago 19
SQL Question

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

Background



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.

Problem



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.

Data



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)



Code



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
rstQuestions.MoveFirst
Do Until rstQuestions.EOF = True
REST OF CODE
Loop
Else
MsgBox "There are no checks against this Analyst"
End If


Issue



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#"

Answer

try to format your criteria as

SELECT * FROM Questions WHERE 
 CheckDate BETWEEN #" 
& format(strFrom,"yyyy/mm/dd") & 
"# AND #"
 & format(strTo,"yyyy/mm/dd") & "#"
Comments