Filter not taking effect

[Emmanuel]

I want to apply a filter using a search button which i applied the folowung codes:


# Sub Search()Dim strCriteria, task As String


If Trim(Me.cboIncomeType.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Please select Payment mode.", buttons:=vbInformation, title:="SOFTWARE"
Me.cboIncomeType.SetFocus
Exit Sub

End If

If Trim(Me.cboIncomeType1.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Please select Payment form.", buttons:=vbInformation, title:="SOFTWARE"
Me.cboIncomeType1.SetFocus
Exit Sub

End If



If IsNull(Me.OrderDateFrom) Then
MsgBox "Please enter the Start date", vbInformation, "Date Range Required"
Me.OrderDateFrom.SetFocus
Else
If IsNull(Me.OrderDateTo) Then
MsgBox "Please enter the End date", vbInformation, "End Date Required"


Me.OrderDateTo.SetFocus


Else
strCriteria = "[DATE] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
" And [DATE] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _
" And[PAYMENT_FORM] LIKE '" & IIf(IsNull(Me.cboIncomeType1), "*", Me.cboIncomeType1) & _
" And[PAYMENT_MODE] LIKE '" & IIf(IsNull(Me.cboIncomeType), "*", Me.cboIncomeType) & "'"


'task = "select * from PAY_FORM_ALL_EXPENSE where (" & strCriteria & ") order by [DATE]" ) #


I initially thought my filter took effect but later realized the "DoCmd.ApplyFilter , strCriteria" was not applying the filter.

Any help to fix this will be highly appreciated.

[June7]

Note for future posts - please place code between CODE tags to retain indentation and readability.

I have never used ApplyFilter method. Review this tutorial http://allenbrowne.com/ser-62.html

[Welshgasman]

Perhaps walk through your code line by line with F8?
Add a Debug.print to make sure the criteria is correct including syntax?

Code posted appears to be missing at least one End If ?

[Emmanuel]

Perhaps walk through you code line by line with F8?
Add a Debug.print to make sure the criteria is correct including syntax?

Code posted appears to be missing at least one End If ?

Forget to add that
There are two end ifs at the end of the code

[ssanfu]

Dim strCriteria, task As String

This line declares strCriteria as a variant.
Only "Task" is declared as a string (but never used).

strCriteria = "[DATE] >= .........."

You have a field named "DATE"?
Date is a reserved word in Access and shouldn't be used for an object name.
Date is JET reserved (kb248738);ODBC (kb125948). Plus it is a built in function.
Plus it is not very descriptive. "Date" of what??? Date Hired? Date fired? Date you got a loan? Date of payment?


Also, should not use spaces or special characters/punctuation in object names.

[Welshgasman]

Forget to add that
There are two end ifs at the end of the code

No good showing part of the code, especially when it does not even look like it can compile, as above.

Examine the filter with debug.print and even walk through to make sure you even get to that line with Applyfilter on it.

Here is an example of Apply filter direct from MS.

 Private Sub tglFilter_Click()    With tglFilter
        If .Value = True Then
            .Caption = "P/T"
            .StatusBarText = "only full-timers"
            DoCmd.ApplyFilter , "[Hours]=40"
        ElseIf .Value = False Then
            .Caption = "All"
            .StatusBarText = "only part-timers"
            DoCmd.ApplyFilter , "[Hours]<40"
        Else
            .Caption = "F/T"
            .StatusBarText = "all employees"
            DoCmd.ShowAllRecords
            .SetFocus 'to activate the button's statusbar-text
        End If
    End With
End Sub

Found at https://docs.microsoft.com/en-us/off...md.applyfilter

[Emmanuel]

This is a zipped copy of my database for analysis.
thanks

[Emmanuel]

Kindly check in my codes and see which modifications can help fix the issue

[pbaldy]

There's a lot wrong. For starters, your form is in data entry mode so filtering it won't result in any records. Use this method:

http://www.baldyweb.com/ImmediateWindow.htm

To figure out the problems in your string. There are 3 if memory serves.

[Micron]

Note for future posts - please place code between CODE tags to retain indentation and readability.

That request has been made of Emmanuel before. Doesn't seem to be willing to accommodate.

[Emmanuel]

That request has been made of Emmanuel before. Doesn't seem to be willing to accommodate.

Just not be able to figure out how is done. It doesn’t work when ever I try it

[Emmanuel]

There's a lot wrong. For starters, your form is in data entry mode so filtering it won't result in any records. Use this method:

http://www.baldyweb.com/ImmediateWindow.htm

To figure out the problems in your string. There are 3 if memory serves.

So how do I go about it?

[pbaldy]

Change the data entry property and then debug the criteria string as shown.

[June7]

Just not be able to figure out how is done. It doesn’t work when ever I try it

Click the # icon on post editor toolbar. Type or copy/paste code between the CODE tags

[Welshgasman]

Just not be able to figure out how is done. It doesn’t work when ever I try it

The method is in my signature each time

Then you will see what is generated, I tend to just type in the codes manually.