apply filter fails on date 1

[eagerl66]

this code is supposed to find records with one or more criteria entered. the txt fields are for the criteria, the names only are fields from the table that are supposed to return the records.

everything that i look for works, but not the date fields. actually, the startdate field works, and then sometimes it doesnt.
what am i doing wrong?

Private Sub cmdFind2_Click()
DoCmd.ApplyFilter "", _
"([RepID] = [Forms]![frmFind2]![txtRepID] " & _
"Or IsNull([Forms]![frmFind2]![txtRepID])) " & _
"And ([StudentName] Like [Forms]![frmFind2]![txtStName] " & _
"Or IsNull([Forms]![frmFind2]![txtStName])) " & _
"And ([StudentID] Like [Forms]![frmFind2]![txtStudentID] " & _
"Or IsNull([Forms]![frmFind2]![txtStudentID])) " & _
"And ([ProgramID] Like [Forms]![frmFind2]![txtProgID]" & _
"Or IsNull([Forms]![frmFind2]![txtProgID])) " & _
"And ([RepName] Like [Forms]![frmFind2]![txtRepName]" & _
"Or IsNull([Forms]![frmFind2]![txtRepName])) " & _
"And ([AmountPaid] Like [Forms]![frmFind2]![txtAmtPaid] " & _
"Or IsNull([Forms]![frmFind2]![txtAmtPaid]))" & _
"And ([EnrolDate] like[Forms]![frmFind2]![txtEnrolD]" & _
"Or IsNull([Forms]![frmFind2]![txtEnrolD])) " & _
"And ([StartDate] like[Forms]![frmFind2]![txtStartDate]" & _
"Or IsNull([Forms]![frmFind2][txtStartDate])) ", "

this code came from Apress pro access 2010 development, page 156

This code calls the ApplyFilter method passing in the selection criteria. For each field that is in the
Form Header, the logic checks to see if the database field matches what was specified or if a value was
not specified. For the fields except CustomerID the comparison uses the Like operator. This allows for a
partial value to be supplied, such as “Ad*”

thanks for your help

 

[dhookom]

I would not allow wildcards in the date field. Then replace the " Like " with " = ".

I typically start with a strFilter and build it based on values from the controls. If there is nothing in the control, I ignore it so it never makes it into the strFilter.

Private Sub cmdFind2_Click()
    Dim strFilter as String
    strFilter = "1 = 1 "
    If Not IsNull(Me.txtRepID) Then
        [green]' assumes RepID is text[/green]
        strFilter = strFilter & " AND RepID like '" & _
		Me.txtRptID & "' "
    End IF
    [green]' more code here for other controls[/green]
    If Not IsNull(Me.txtStartDate) Then
        strFilter = strFilter & " AND StartDate = #" & _
		Me.txtStartDate & "# "
    End IF
    Me.Filter = strFilter
    Me.FilterOn = True

Duane
Hook'D on Access
MS Access MVP

 

[eagerl66]

hi there
not yet successful with the finding of the date

could you pls explain the purpose of this statement?
strFilter = "1 = 1 "
thanks very much.

 

[dhookom]

The "1 = 1 " is just a place holder with no effect. It allows us to add more criteria with " AND ..." That's all. Nothing more. Just lazy programming to get the job done.

Does your start date include a time element or just a date?

Duane
Hook'D on Access
MS Access MVP