Combining two filters (1 Viewer)

ECEK · Dec 6, 2018

Could someone help me to apply two filter into one please?
I have tried several iterations but to no avail.

Both work independently.

Code:

DoCmd.ApplyFilter , "Status = '...awaiting decision'"
DoCmd.ApplyFilter , "customer = [Forms]![frmSettings]![UserName]"

I tried this which doesn't create an error but returns no results

Code:

 DoCmd.ApplyFilter "", "[Status] Like '...awaiting decision' And_
 [customer ] Like '[Forms]![frmSettings]![UserName]'"

As always your invaluable advice is much appreciated.

Minty · Dec 6, 2018

Create the filter string separately so you can debug it - something like

Code:

Dim sFilter as String

sFilter =  "[Status] Like '...awaiting decision' And [customer ] = '" & [Forms]![frmSettings]![UserName] &"'"

Debug.Print sFilter

DoCmd.ApplyFilter sFilter

isladogs · Dec 6, 2018

In the ones that work you use = sign.
Why not combine still using =.
If you want to use Like then wildcards * may be needed but will make your code slower...possibly much slower.
Which do you need?

ECEK · Dec 6, 2018

Hi Minty
I'm only getting the UserName results

isladogs...not bothered as long as it works ! lol

Minty · Dec 6, 2018

ECEK said:
Hi Minty
I'm only getting the UserName results

isladogs...not bothered as long as it works ! lol

Well - is the status really "'...awaiting decision" ?

In which case use =
sFilter = "[Status] = '...awaiting decision' And [customer] = '" & [Forms]![frmSettings]![UserName] &"'"

By using the And , if both conditions aren't satisfied , you shouldn't get any results, so your statement about only getting the user names is puzzling ?

ECEK · Dec 6, 2018

Fixed in !!
Thanks for you time guys. It pointed me in the right direction. My solution

Code:

Dim FilterStatus As String
Dim FilterCustomer As String

FilterStatus = "Status = '...awaiting decision'"
FilterCustomer= "Customer = [Forms]![frmSettings]![UserName]"

Me.Filter = Me.Filter = FilterStatus & " And " & FilterCustomer
Me.FilterOn = True
 
Me.Filter = FilterPara & " And " & Filteradviser

isladogs · Dec 6, 2018

Shouldn't Me.FilterOn=True be the final line so it includes all the filter criteria?
Or is that a separate instance of filtering being started?

Combining two filters (1 Viewer)

ECEK

Registered User.

Minty

AWF VIP

isladogs

MVP / VIP

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread