Access & VBA Dates Between

[bigal.nz]

Hi All,

I am trying to execute a query in VBA between dates, I have tested the query in the SQL window and it works, but when I try it via VBA I only ever get one record back, and I should get several.



Private Sub Command11_Click()
Dim rst As DAO.Recordset

Dim strSQL As String
strSQL = "SELECT Event, DateStart FROM SAM WHERE DateStart BETWEEN #03/01/2016# AND #03/15/2016#;"
Set rst = CurrentDb.OpenRecordset(strSQL)
' new code:
Debug.Print strSQL

MsgBox (rst.RecordCount)
MsgBox (rst!EventNo)
MsgBox ("EventSQL")
rst.Close
Set rst = Nothing


End Sub



Can anyone tell me what I am doing wrong? I would expect rst.recordcount to be about 5 records - not 1.

Note I am in NZ where we use dd/mm/yyyy - but everything I have read tells me for the purposes of SQL in VBA use U.S. format of mm/dd/yyyy so in the code above it is U.S. format.

The values in DateStart are:




DateStart


17/02/2016


9/03/2016 8:00:00 a.m.


1/03/2016


1/03/2016 7:29:00 p.m.


3/03/2016


12/03/2016 6:30:00 a.m.


15/03/2016 2:00:00 a.m.




Cheers

-AL

[bigal.nz]

I had to do
rst.MoveLast
rst.Recordcount

Recordcount only gives you the number of records accessed, so you need to go to last first.

[SamT]

Access Dates are Strings, Excel Dates are Doubles.

In Excel convert Access Dates to Excel dates with the CDate Function and convert Excel Dates to Strings with the Format Function.