combine filter

[Song Su]

I have a control in subform with before update event below. I want to add
additional filter (date range) that user entered in two controls (From and
To) in main form named MainMenu. How to add this additional filter to 'Set
rst' line?

Private Sub Sect_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("Select * From TrackData Where [sect]
= '" & Me.Sect & "'")
If rst.EOF Then
MsgBox Me.Sect & " Is not valid Sect or no student signed in during
the period specified."
Cancel = True
Else
Me.Course = rst![Course]
End If
Set rst = Nothing

Exit_sect_BeforeUpdate:
Exit Sub
Err_sect_BeforeUpdate:
MsgBox Err.Description
Resume Exit_sect_BeforeUpdate

End Sub

 

[Ofer Cohen]

To add a date criteria you need to add # before and after the date value
(just as you added the single quote ' before the text), something like

If you have only one date field in the table use something like:

Set rst = CurrentDb.OpenRecordset("Select * From TrackData Where [sect]
= '" & Me.Sect & "' And [Date Field name] Between #" & Me.[Start Date] & "#
And #" & Me.[End Date] & "#")

If you have two date fields in the table (start abd end) use something like

Set rst = CurrentDb.OpenRecordset("Select * From TrackData Where [sect]
= '" & Me.Sect & "' And [Start Date Field name] <= #" & Me.[Start Date] & "#
And [End date field name] >= #" & Me.[End Date] & "#")