Where Criteria string help

  • Thread starter Thread starter Alan Fisher
  • Start date Start date
A

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!
 
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\#") & ";"
 
Alan Fisher said:
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!
Click to expand...

I don't know anything about the Calendar control, but assuming that its
Value property is returning the selected date, then I'd think this would
work:

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