Access Button - Apply 2 Filters

[SantasLittleHelper]

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?

 

[mole999]

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

 

[SantasLittleHelper]

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?

 

[welshgasman]

Try

Me.Filter = "BONum = " & Me.BONum & " AND Status =''"
Me.Filteron = True

adjust names to suit. Status has 2 single quotes

HTH

 

[SantasLittleHelper]

Try

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.

 

[welshgasman]

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 ?

 

[SantasLittleHelper]

Here's the code I'm using:

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.

 

[mole999]

can you get your intended record if you don't check for status ?

 

[SantasLittleHelper]

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)

 

[mole999]

so testing will mean you can apply both options successfully before you combine them