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

Question

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

Have you tried, just 1 criteria, less than, it could be your dates are becoming US like 12/1/2015 — Nathan_Sav
– Nathan_Sav, Commented Jun 22, 2016 at 9:50
If you type that up as an answer, I'll accept it as it's working now. — Petay87
– Petay87, Commented Jun 22, 2016 at 10:22

nazark · Accepted Answer · 2016-06-22 10:27:07Z

1

try to format your criteria as

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

answered Jun 22, 2016 at 10:27

nazark

1,2402 gold badges10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

Background

Problem

Data

Code

Issue

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

Background

Problem

Data

Code

Issue

1 Answer 1

Comments

Related