Filter SubFrom from Main Form using date

[StonyfieldRob]

Neither form is bound and only the subform is pulling data from a table.

Would like to enter a date on the main form [Date1].

The subform would then show all data with a start date [SDate] less than
[Date1].
And an end date [EDate] great than [Date1].

 

[Jeanette Cunningham]

Here's some sample code to get you started.

Private Sub FilterTheSubform
Dim strWhere As String

Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a
JET query string.


'Date field example. Use the format string to add the # delimiters and get
the right international format.
If Not IsNull(Me.[Date 1]) Then
strWhere = strWhere & "([SDate] >= " & Format(Me.[Date 1],
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.[Date 1]) Then 'Less than the next day.
strWhere = strWhere & "([EDate] < " & Format(Me.[Date 1] + 1,
conJetDate) & ")"
End If


With Me.SubformControlName.Form
.Filter = strWhere
.FilterOn = True
End With

End Sub


Note: replace my object names with your own.
For a more complete understanding of how to create a search screen, download
this sample db.
http://allenbrowne.com/ser-62.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia