Filter records in a forms where one or more fields are blank

Hi darls15,

You could toggle the forms underlying recordsource property to show the records you need.

The recordsource property value for you form is currently probably the table tblRegistration.

You could create a new query named qryRegistration_fltr with something like the following sql:

SELECT * from [tblRegistration]
WHERE
[Response1] = NULL AND [Response2] = NULL

Save it as qryRegistration_fltr.

Create a new form and Name it Form1
Create a new form (or use your exiting form it you can) and set this forms recordsource to [tblRegistration]. Name this form frmRegistration_DATA.

Add frmRegistration_DATA to Form1 as a subform.

On Form1,   add a toggle button control - name it togSrc.
On the toggle button click event add code similar to the following:

    If Me.togSrc Then
        Me.togSrc.Caption = "Click To Remove Filter"
        Me.frmRegistration_DATA.Form.RecordSource = "qryRegistration_fltr"
        Me.Requery
    Else
        Me.togSrc.Caption = "Click To Set Filter"
        Me.frmRegistration_DATA.Form.RecordSource = "tblRegistration"
        Me.Requery
    End If


Add code to the onOpen event of Form1 to reset the recordsource property to tblRegistration.

You should now be able to set and unset the filter using the toggle button.


Rgds,
Jonathan

[Response1] = NULL AND [Response2] = NULL

You cannot compare anything to null using this syntax. The result would be Null rather than True or False regardless of the value of the Response field.  In VBA, use the IsNull() function.  In SQL, use the Is Null predicate.  

I believe the question was for any of the responses to be empty so you would use OR rather than AND.  

If response is a text field and you have not specifically disallowed ZLS (Zero Length Strings), you have to account for them in the where clause (or filter)

Where response1 & "" = "" OR response2 & "" = "" OR ....

By concatenating a ZLS to the field, Access converts a null value to a ZLS so you only have to check for ZLS.  Otherwise, you would need to check for both.

To expand a little further on both comments Jonathan and Pat ...

Try this for your filter query: qryRegistration_fltr

SELECT * from [tblRegistration]
WHERE
Nz(Response, 0) = 0 Or Nz(Response2, 0) = 0 Or Nz(Response3, 0) = 0 Or Nz(Response4, 0) = 0 Or Nz(Response5, 0) = 0 Or Nz(Response6, 0) = 0 Or Nz(Response7, 0) = 0;


Try this for your Toggle Button OnClick Event:

 If Me.togSrc True Then
        Me.togSrc.Caption = "Click To Remove Filter"
        Me.RecordSource = "qryRegistration_fltr"
        Me.Requery
    Else
        Me.togSrc.Caption = "Click To Set Filter"
        Me.RecordSource = "tblRegistration"  
        Me.Requery
    End If



ET

Using Nz() only checks for null.  It does not handle ZLS which is usually a possibility with text fields that are in imported files.

Pat,

Yes - Totally agree  - I did think that of the ZLS but forgot to include the test and yes of course OR is needed rather than AND.

This is a neat solution
Where response1 & "" = "" OR response2 & "" = "" 


etsherman,

I assumed that form showing the records was a datasheet .

Rgds,
Jonathan

Pat ...

<<<Using Nz() only checks for null.  It does not handle ZLS which is usually a possibility with text fields that are in imported files.>>>

This will work as well and how I originally wanted to post it ... the 0 was just force of habbit.

Nz(Response, "") =""

ET

You are missing the point.  The NZ() functions only checks for nulls.

So
NZ("", 0) = 0 - returns false
NZ(null, 0) = 0 - returns true

A text field that is "empty" could be null or a ZLS - UNLESS you have specifically disallowed ZLS which most people don't do.

Pat ...

This does indeed check for Nulls and Zero Length Strings!!!  Did you try it???  I've used this many times with much success!

Nz(Response, "") =""

ET

I didn't have to.  But I did create an example for you to show you that it doesn't.
I'm pretty sure that you don't actually have any ZLS and so NZ() seems to work but trust me, it doesn't work for anything except nulls.

Thank you all for helping me out here, such great advice which is helping me learn! I'll test your suggestions in my database and let you know how it goes.

Sorry.  I didn't notice that you had changed your suggested expression From Nz(xx, 0) = 0 to Nz(xx, "")= "".  So, yes, if you convert nulls to ZLS, then you can test for ZLS.  That is what the expression I posted does without having to use a function.  I used your original expression in the example I posted.  I think we must be done with this now.

Hi All

I've tried to implement your suggestions, however I can't seem to get this to work.

My filter query is working well, however, I can't get the code behind the toggle button to work and am getting a compile error.

Can you please help me out with what I may have done wrong?

I've attached a sample database of what I have done so far.

Thanks
darls15
ResponseDB.accdb

First off, my hats off to all of you! I can't thank you all enough for your time and willingness to help me out here. As your suggestions have equally contributed to my problem being solved I will split the points as equally as possible. Thanks again, you guys just rock!

Cheers