Filter Form to get a data relating to a date field

  • Thread starter Thread starter SarahEGray
  • Start date Start date
S

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
 
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
 
Back
Top