From: StonyfieldRob on
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].
From: Jeanette Cunningham on
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


"StonyfieldRob" <StonyfieldRob(a)discussions.microsoft.com> wrote in message
news:B63C226A-8C9A-4909-B66F-F14CFB6A3820(a)microsoft.com...
> 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].