Filter Button on Split Form

[data808]

I have this code set to a button on a split form. It filters out all the blank records for the "Status" column.




Private Sub cmdFilterPendingStatus_Click()

Me.Filter = "[Status]"
Me.FilterOn = True
Me.Requery
Me.txtFilterNamePlate.SetFocus

End Sub

I would like to do the exact opposite and filter out everything except for the blank records in the "Status" column.


How can I change the code to do this? I guess I should also mention that the "Status" field is a long text/memo box but I can easily change this to short text if I really have to. I realize that short text can do the blank filtering within the split form itself by clicking on that tiny arrow in the column header but I am trying to make it really user friendly where the user can just click a button to do this. The long text box has some options to filter but not for blank fields in a record which makes me think that this may not be possible to do if the Status field remains a long text box. Some examples for the filtering options that are available in the split form for a long text box are: Begins with, contains, ends with, etc... however, when I type "" to try and filter out everything except the records that have null in the Status field, it says that "" is not valid. Anyway, sorry for making this so long. I think you guys get the picture.

What can I do to get the results I need?

[Rawb]

Generally speaking, you shouldn't have Columns with blank values because it makes querying more complex (case in point).

Assuming you're actually allowing the Status Column to be completely devoid of anything, the following should work.

Private Sub cmdFilterPendingStatus_Click()
    Me.Filter = "[Status] IS NULL"
    Me.FilterOn = True
    Me.Requery
    Me.txtFilterNamePlate.SetFocus
End Sub

[Perceptus]

I have had to do this recently in access 2007. What I did was

filter the data to how I wanted to have it displayed.
And then Save the Form.
Next design the form and look at the filter property for the form, copy this value to your clipboard.
make a macro that does "Apply Filter"
in the where condition paste the value you previous copied.
you can leave the other 2 fields for the macro (filter name and control name) empty.
save the macro.

then add a command button to the form, using the wizard set the command button to run the macro you just created.

[data808]

Thanks I will try this later and let you know how it works out. Thanks again.

[data808]

Thanks it worked really well. Can't believe something to simple was so hard for me. I was trying to use that NULL in every combination I thought possible and could not figure it out. Thanks for the help.