Where Criteria string help

[Alan Fisher]

I need to create a recordset using VBA but which I can do
with no problem. I want to be able to limit the records to
the month selected by a calandar control. I have tried
doing this using a couple of different methods like
DatePart() but with no success. Anyone have a way to
accomplish this. Here is a sample of one failed attempt:

Set rs = db.OpenRecordset(" SELECT * FROM tblHeloMisnSched
WHERE '" & DatePart("m", [DateStart]) & "'" = _
& Month(Me.Calendar.Value) & "'", dbOpenSnapshot)

Thanks for any help!

 

[Allen Browne]

Several suggestions:
1. To get the first of the month, subtract the Day of the month and add 1.

2. The last of the month is one month later less 1.

3. The date in the SQL statements needs to be in mm/dd/yyyy format, and
delimited with #.

4. The Where clause needs a field name.

Result:

Dim dtStart As Date
Dim strSQL As String
dtStart = Me.Calendar.Value - Day(Me.Calendar.Value) + 1
strSQL = "SELECT * FROM tblHeloMisnSched " & _
"WHERE [YourDateFieldNameHere] Between " & _
Format(dtStart, "\#mm\/dd\/yyyy\#") & " And " & _
Format(DateAdd("m", 1, dtStart) - 1, "\#mm\/dd\/yyyy\#") & ";"