I have a subform based on a table "Team_Selection_Notes" which has three fields:
- PersonID (Number - the link field)
- NoteDate (Date/Time) and
- Comment (Short text)).
Expand|Select|Wrap|Line Numbers
- Private Sub Oldies_Click()
- '
- ' Routine to display only records older than a certain age in the subform
- '
- Dim EarlyDate As Date, MinimumAge As Integer, strSQL As String
- On Error GoTo ErrorProc
- MinimumAge = 6
- EarlyDate = DateSerial(Year(Now) - MinimumAge, Month(Now), Day(Now))
- Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.Filter = "[NoteDate] < " & EarlyDate
- Forms![18: Team Selection Notes]![Subfrm_TSNotes].Form.FilterOn = True
- ExitSub:
- Exit Sub
- ErrorProc:
- MsgBox "Error " & Err.Number & ": " & Err.Description, , "Searching for old records"
- Resume ExitSub
- End Sub
A particular person has 3 notes, dated 22 Aug 2013, 22 Aug 2013 and 18 Feb 2018. With the cut-off age set at 6 years the filter prevents any of the records being displayed. If I change the inequality in the filter from < to >, then all three records are displayed.
I must be missing something obvious, but what is it?