Access Button - Apply 2 Filters

SantasLittleHelper

Board Regular
Joined
Nov 25, 2016
Messages
77
I'm trying to create a button (on a form) which applies 2 different filters at the same time.

1. Only Show records which have the same 'BO' Number as the current record
AND
2. Only Show records where 'Status' is Blank

I've tired a few ways but I can't see to get it to work.

Does anybody have any suggestions?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
haven't played with ACCESS for Ages, in SQL it would be like

SELECT *
FROM Data
WHERE BO Number = whatever the current record is
AND Status <> NULL

Test each part separately
 
Upvote 0
What would I put instead of "Whatever the current record is?" Is there a specific word/phrase I need to use so it filters on the current record?
 
Upvote 0
Try
Code:
Me.Filter = "BONum = " & Me.BONum & " AND Status =''"
Me.Filteron = True

adjust names to suit. Status has 2 single quotes

HTH
 
Upvote 0
Try
Code:
Me.Filter = "BONum = " & Me.BONum & " AND Status =''"
Me.Filteron = True

adjust names to suit. Status has 2 single quotes

HTH

When I tried this, a pop-up appears "Enter the Parameter Value" and then it show's the BONum above the text box. I've typed the current BONum and it filters to show a blank record. Do you know what's gone wrong? - I've copied the Code you've used but replaced the field names accordingly.
 
Upvote 0
BoNum is the name I gave to the BO Number, you have to change it to your names, which I do not know.

That is why I said change names to suit.

I also assumed that Status was text, so try that as one space ' '

Could the Status be NULL ?
 
Last edited:
Upvote 0
Here's the code I'm using:
Code:
Private Sub Command121_Click()
[COLOR=#333333][I]Me.Filter = "BO = " & Me.BO & " AND Status =''"
[/I][/COLOR][COLOR=#333333][I]Me.Filteron = True
End Sub
[/I][/COLOR]

I've tried it with the space for Status (' ') but that doesn't work either. The Status field can be null.

I've used the Macro builder on Access to create 'IsNull(Status)' which filters correctly.
 
Last edited:
Upvote 0
can you get your intended record if you don't check for status ?
 
Upvote 0
can you get your intended record if you don't check for status ?

Yes. But I'd like to be able to filter to exclude any records which have a Status. (e.g. Filtering by the BO, returns 100 records... 25 will have a Status - So ideally I'd like it to only display 75 records.)

Edit:
Sorry I may have misunderstood you.. Based on the code above, I can't get to my intended records.. it pops up with the Enter Parameter Value Box and then returns a blank record. (Empty Form)
 
Last edited:
Upvote 0
so testing will mean you can apply both options successfully before you combine them
 
Upvote 0

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top