Form Filter "Missing Data" Using VBA

[r0v3rT3N]

Hello, I have a continuous form and some of data is missing. (I'm making a Contacts Database)
How would I filter out the missing info using the "Form Filter" feature.
P.S, I want it so that when I click an option button it filters.
Here is the code I currently have.

Private Sub Option25_Click()    
    Me.Filter = " "
    Me.FilterOn = True
End Sub

Thanks,
rT

[Robeen]

I think your code:
Me.Filter = " "
. . . is not complete.

Try something like this:
Me.Filter = "[CustomerID] = 123"

or - if the CustomerID is Text [Alpha Numeric] . . .
Me.Filter = "[CustomerID] = 'Joe123'"

Hope this helps!

[r0v3rT3N]

As I said before it's a contact database, and multiple people have missing information.
I left the code missing as I didn't know what to write.
Is their anyway to maybe Name missing info?
So I just call it a certain name, and anything missing will automatically be called that, so the filter can find it?

[r0v3rT3N]

I would really appreciate an answer before 2:15.

[Robeen]

Are you saying you want to see all records where there is NO value in one particular field?

[Robeen]

When you have this:
Me.Filter = " "
. . .
What you are saying is that your filter is just one space character . . . It's not really any filter at all.

A filter is supposed to tell Access that you are looking for a certain value in a certain field in the table . . . OR that you are looking for the ABSENCE of any value in that field in the Table.
Either way - you have to tell Access Which Field you want to apply the filter to - and then what you want to look for in that field.

So . . . 2 questions:
1. What is the Field Name - in your Table - that has missing data?
2. What type of data is in that Field in your Table [in Table Design View you should see 'Text' . . . 'Number' . . . 'Date/Time' . . . 'Currency' . . .].

[r0v3rT3N]

1. It's a Contact database. So anything I don't know will be left blank. So there is a chance that all the fields (at some point) could be left blank.
2. Text.

[Robeen]

Ok . . . I need one field name that you would like to filter on to see if it is 'blank'.

For instance - let's say your field name is CustomerName - ok? And that it might be not filled in. And that you want to see all records where the CutsomerName is 'missing'.
In Access [and other Databases] if there is no value in a field, there might actually be a Null in there - OR - there might be an empty string [""] - two DoubleQuotes with no space between them.
Let's assume that in your Database when there is no name in the CustomerName field - it is Null.
In that case, your filter might look like this:
Me.Filter = "[CustomerName] Is Null"
Me.FilterOn = True

I hope this helps.

[Robeen]

If you have more than one field you want to apply the filter to [in order to show ALL rows of data that have ANY fields blank . . . then you will have to specifically filter for each field that may have blanks in it . . . for example:
Me.Filter = "[CustomerName] Is Null OR [Address] Is Null OR [ZipCode] Is Null OR [EmailAddr] Is Null"
Me.FilterOn = True

[r0v3rT3N]

Can you tell me how to do it for the categories "First Name", "Last Name", "Street Address", "City", "Phone Number", "E-Mail", and "Relationship.
Much appreciated.
rT

[Robeen]

Me.Filter = "[First Name] Is Null OR [Last Name] Is Null OR [Street Address] Is Null OR [City] Is Null OR [Phone Number] Is Null OR [E-Mail] Is Null OR [Relationship] Is Null"

Hope this helps.

P.S. Try not to have spaces in your FieldNames.

[r0v3rT3N]

THANK YOU VERY MUCH!
P.S, I heard about the non-spaced field names, however, forgot to implement them!
rT

[Robeen]

You're welcome.
Mark this 'Solved' if it has solved your problem [Top of Page . . . Thread Tools . . .].