Filtering a sub form

[Laura]

I have a subform that I need to be able to filter based on
the choice made from an option group.

I'm lousy at filters and getting all the '&' and
quotations in the right place.

Does anyone know an online resource I could look at?

Basically it's a bunch on dates.

I want the user to be able to only show

1. Dates occurring in the current month.
2. Dates occurring in the current year.
3. Dates occurring between the start and end date on the
master form.

I have a field called 'Datework' that will be the item
being filtered.

Please help!

Thanks in advance,
Laura

 

[Allen Browne]

This example shows how to:
- Calculate the first and last date for each period.
- Format the dates for the filter string.

---------------------code starts----------------------
Dim dtStart As Date 'First date in period
Dim dtEnd As Daate 'Last date in period
Dim strFilter As String
Dim strError As String
Const conJetDate ="\#m\/dd\/yyyy\#"

If Me.Dirty Then 'Save before filter.
Me.Dirty = False
End If

Select Case Me.[MyOptionGroup].Value
Case 1 'current month
dtStart = Date - Day(Date) + 1
dtEnd = DateAdd("m", 1, dtStart) - 1
Case 2 'current year
dtStart = DateSerial(Year(Date), 1, 1)
dtEnd = DateSerial(Year(Date), 12, 31)
Case 3
With Me.Parent
If IsNull(!Start) OR IsNull(!End) Then
strError = "Both dates required on parent form."
Else
dtStart = Me.Parent![Start]
dtEnd = Me.Parent![End]
End If
End With
Case Else
strError = "Don't know what to do with option " & Me.[MyOptionGroup]
End Select

If Len(strError) > 0 Then
MsgBox strError
Else
strFilter = "[WorkDate] Between " & Format(dtStart, conJetDate) & " And
" & Format(dtEnd, conJetDate)
Me.Filter = strFilter
Me.FilterOn = True
End If
---------------------code ends----------------------