Filter Form to get a data relating to a date field

[SarahEGray]

I have created a continuous form within an Internal Audit database and want
to be able to filter by a From and To Date range and a name. I have set it
up and at the back entered the following code to the Apply Filter button but
I keep getting "Variable not Defined". I have tried lots of things but
cannot sort it. Any suggestions? I have put a note of the code used below.

Private Sub Command9_Click()
Me.Filter = "1=1"

'Response Due Date
If Not IsNull(Me.txtResponseDateFrom) Then
Me.Filter = Me.Filter & " AND [Responseduebydate] >= #" &
FormatDateTime(Me.txtResponseDateFrom.Value, vbLongDate) & "#"
End If
If Not IsNull(Me.txtResponseDateTo) Then
Me.Filter = Me.Filter & " AND [Responseduebydate] >= #" &
FormatDateTime(Me.txtResponseDateTo.Value, vbLongDate) & "#"
End If
If Not IsNull(Me.txtContact) Then
Me.Filter = Me.Filter & " AND [PersonContacted]= '" & Me.txtContact
& "'"
End If
Me.FilterOn = True
Me.Requery

End Sub

 

[roger]

Two things,

One it looks like your storing (or inputting) dates as text, and the
formating them as dates AS you perform calculations on them. Which is a
syntax nightmere.

Dates need to be stored and inputted as dates. DateTime datatype.

Then I would use DateDiff() to calculate if one date was between two other
dates somthing like:

Me.Filter = "DateDiff("d", [Responseduebydate],"_
& Me.ResponseDateTo.Value & ")>1"

or less than zero, or whatever.

See DateDiff in VB help

hth roger