Thanks, folks. Sorry it's taken me so long to reply. I did get this to work, but I had to pick up a VBA for Access for Dummies book in order to get it going. Took a while =P But yes, what I ended up doing was checking to see if my date fields were blank, and if not, concatenating the date information to a Variant that would serve as a WHERE clause. This WHERE clause can also contain all the other conditions (FirstName, LastName, ProjectID, etc.). I also added a subform for Preview purposes. The code looks like this:
Code:
Private Sub InstantSearch()
'Update the record source
Me.subMainTbl.Form.RecordSource = "SELECT * FROM qrySearch " & BuildFilter
'Requery the subform
Me.subMainTbl.Requery
End Sub
'_________________________________________________________________________________
Private Function BuildFilter() As Variant
Dim varWhere As Variant: varWhere = Null
If Me.startDateBefore > "" Then
varWhere = varWhere & "[StartDate] <= #" & Me.startDateBefore & "# And "
End If
If Me.startDateAfter > "" Then
varWhere = varWhere & "[StartDate] >= #" & Me.startDateAfter & "# And "
End If
If Me.endDateBefore > "" Then
varWhere = varWhere & "[EndDate] <= #" & Me.endDateBefore & "# And "
End If
If Me.endDateAfter > "" Then
varWhere = varWhere & "[EndDate] >= #" & Me.endDateAfter & "# And "
End If
If IsNull(varWhere) Then 'Do nothing
Else
'Tidy Main Filter
If Right(varWhere, 5) = " And " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
varWhere = "WHERE " & varWhere
End If
End If
BuildFilter = varWhere
End Function
Many thanks to Gromit, who provided the skeleton that got me started. http://www.access-programmers.co.uk/...ad.php?t=99353