CODEWITHSUNDEEP
ms-accessvba
Felps
Felps

Reputation: 23

Multiple filters on a subform

Trying to apply 2 filters at the same time to a subform.

Want to see records between DATES X and Y, and from BRANCH Z only.

Working fine alone, but can't use both at the same time. I know it's something

Current code:

Private Sub Command39_Click()
If IsNull(Me.txtFrom) Or IsNull(Me.txtTo) Then
    MsgBox "Insert date!"
Else
    With Me.frmDailyRevenue.Form
        .Filter = "[DateDbl] BETWEEN " & Me.txtFromDbl & " AND " & Me.txtToDbl & "" And [F5] = " & Me.cboBranch & """
        .FilterOn = True
    End With
  End If 
End Sub

This is basically bits of code I got from the web as I'm really new to this.

So, all advice is welcome.

Upvotes: 0

Views: 729

Answers (2)

Felps
Felps

Reputation: 23

Found the problem.

Using BETWEEN and AND for the date range was causing some conflict with the second AND to add the filter for field F5.

So I switched to use >= and <= as follows:

.Filter = "[DateDbl] >= " & Me.txtFromDbl & " AND [DateDbl] <= " & Me.txtToDbl & " AND [F5] = " & Me.cboBranch & ""

Just to clarify, for people that might come for this later, you should use # as Sergey pointed out if you have a date field, my date is in double format, so I don`t need to.

Thanks,

Upvotes: 1

Sergey S.
Sergey S.

Reputation: 6336

Try this:

.Filter = "[DateDbl] BETWEEN #" & Format(Me.txtFromDbl,"mm\/dd\/yyyy") & _
     "# AND #" & Format(Me.txtToDbl,"mm\/dd\/yyyy") & "# And [F5] = '" & Me.cboBranch & "'"

I supposed that Me.cboBranch is text. If this field contains code, remove single quotes.

Also I noticed that controls you check and controls you take data from are different (Me.txtFrom and Me.txtFromDbl, Me.txtTo and Me.txtToDbl), check this.

Upvotes: 2

Related Questions