<div>
Hi darls15,<br />
<br />
You could toggle the forms underlying recordsource property to show the records you need.<br />
<br />
The recordsource property value for you form is currently probably the table tblRegistration.<br />
<br />
You could create a new query named qryRegistration_fltr with something like the following sql:<br />
<br />
SELECT * from [tblRegistration]<br />
WHERE<br />
[Response1] = NULL AND [Response2] = NULL<br />
<br />
Save it as qryRegistration_fltr.<br />
<br />
Create a new form and Name it Form1<br />
Create a new form (or use your exiting form it you can) and set this forms recordsource to [tblRegistration]. Name this form frmRegistration_DATA.<br />
<br />
Add frmRegistration_DATA to Form1 as a subform.<br />
<br />
On Form1, &nbsp; add a toggle button control - name it togSrc.<br />
On the toggle button click event add code similar to the following:<br />
<br />
&nbsp; &nbsp; If Me.togSrc Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.togSrc.Caption = &quot;Click To Remove Filter&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.frmRegistration_DATA.Fo<wbr />rm.RecordS<wbr />ource = &quot;qryRegistration_fltr&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.Requery<br />
&nbsp; &nbsp; Else<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.togSrc.Caption = &quot;Click To Set Filter&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.frmRegistration_DATA.Fo<wbr />rm.RecordS<wbr />ource = &quot;tblRegistration&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.Requery<br />
&nbsp; &nbsp; End If<br />
<br />
<br />
Add code to the onOpen event of Form1 to reset the recordsource property to tblRegistration.<br />
<br />
You should now be able to set and unset the filter using the toggle button.<br />
<br />
<br />
Rgds,<br />
Jonathan
</div>


<div>
<blockquote>
[Response1] = NULL AND [Response2] = NULL<br />

</blockquote>
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. &nbsp;In VBA, use the IsNull() function. &nbsp;In SQL, use the Is Null predicate. &nbsp;<br />
<br />
I believe the question was for any of the responses to be empty so you would use OR rather than AND. &nbsp;<br />
<br />
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)<br />
<br />
Where response1 &amp;&nbsp;&quot;&quot; = &quot;&quot;&nbsp;OR response2 &amp;&nbsp;&quot;&quot; = &quot;&quot;&nbsp;OR ....<br />
<br />
By concatenating a ZLS to the field, Access converts a null value to a ZLS so you only have to check for ZLS. &nbsp;Otherwise, you would need to check for both.
</div>


<div>
To expand a little further on both comments Jonathan and Pat ...<br />
<br />
Try this for your filter query: qryRegistration_fltr<br />
<br />
SELECT * from [tblRegistration]<br />
WHERE<br />
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;<br />
<br />
<br />
Try this for your Toggle Button OnClick Event:<br />
<br />
&nbsp;If Me.togSrc True Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.togSrc.Caption = &quot;Click To Remove Filter&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.RecordSource = &quot;qryRegistration_fltr&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.Requery<br />
&nbsp; &nbsp; Else<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.togSrc.Caption = &quot;Click To Set Filter&quot;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.RecordSource = &quot;tblRegistration&quot; &nbsp;<br />
&nbsp; &nbsp; &nbsp; &nbsp; Me.Requery<br />
&nbsp; &nbsp; End If<br />
<br />
<br />
<br />
ET
</div>


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


<div>
Pat,<br />
<br />
Yes - Totally agree &nbsp;- I did think that of the ZLS but forgot to include the test and yes of course OR is needed rather than AND.<br />
<br />
This is a neat solution<br />
Where response1 &amp;&nbsp;&quot;&quot; = &quot;&quot;&nbsp;OR response2 &amp;&nbsp;&quot;&quot; = &quot;&quot;&nbsp;<br />
<br />
<br />
etsherman,<br />
<br />
I assumed that form showing the records was a datasheet .<br />
<br />
Rgds,<br />
Jonathan
</div>


<div>
Pat ...<br />
<br />
&lt;&lt;&lt;Using Nz() only checks for null. &nbsp;It does not handle ZLS which is usually a possibility with text fields that are in imported files.&gt;&gt;&gt;<br />
<br />
This will work as well and how I originally wanted to post it ... the 0 was just force of habbit.<br />
<br />
Nz(Response, &quot;&quot;) =&quot;&quot;<br />
<br />
ET
</div>


<div>
You are missing the point. &nbsp;The NZ() functions only checks for nulls.<br />
<br />
So<br />
NZ(&quot;&quot;, 0) = 0 - returns false<br />
NZ(null, 0) = 0 - returns true<br />
<br />
A text field that is &quot;empty&quot; could be null or a ZLS - UNLESS you have specifically disallowed ZLS which most people don't do.
</div>


<div>
Pat ...<br />
<br />
This does indeed check for Nulls and Zero Length Strings!!! &nbsp;Did you try it??? &nbsp;I've used this many times with much success!<br />
<br />
Nz(Response, &quot;&quot;) =&quot;&quot;<br />
<br />
ET
</div>


<div>
I didn't have to. &nbsp;But I did create an example for you to show you that it doesn't.<a href="https://filedb.experts-exchange.com/incoming/2016/07_w31/1108751/nz2.JPG" target="_blank" title="nz2.JPG (44 KB)"><img alt="nz2.JPG" class="file-block lazyload" style="max-width: 373px;" data-src="https://filedb.experts-exchange.com/incoming/2016/07_w31/1108751/nz2.JPG" /></a><br />
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.
</div>


nz2.JPG

<div>
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.
</div>


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


<div>
Hi All<br />
<br />
I've tried to implement your suggestions, however I can't seem to get this to work.<br />
<br />
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.<br />
<br />
Can you please help me out with what I may have done wrong?<br />
<br />
I've attached a sample database of what I have done so far.<br />
<br />
Thanks<br />
darls15<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w31/1108840/ResponseDB.accdb" target="_blank" class="file-inline" title="ResponseDB Example (568 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>ResponseDB.accdb</a>
</div>


ResponseDB.accdb

ResponseDBUpdated.accdb

<div>
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!
</div>


<div>
<div class="content wysiwyg-content">
Hi Experts<br />
<br />
I have a table [tblRegistration] which is imported into my MSAccess database from a system which has no mandatory controls. This table will be continually added to as the data is regularly exported.<br />
<br />
To assist in cleaning up the data, I've built a form, [frmRegistration], based on the this table and it includes 7 text fields which need to be checked to ensure data exists in all fields for every record.<br />
<br />
I need to filter the recordset to only show only those records containing any blank fields. The fields are named Response1, Response2 ... and so on.<br />
<br />
Ultimately, I would like to filter these records using a toggle button on the form so that I can turn the filter on and off again when required.<br />
<br />
Would anyone be able to help me accomplish this? I have searched and haven't been able to find anything that works the way I need it to.<br />
<br />
Thanks<br />
darls15
</div>
</div>


Hi Experts

I have a table [tblRegistration] which is imported into my MSAccess database from a system which has no mandatory controls. This table will be continually added to as the data is regularly exported.

To assist in cleaning up the data, I've built a form, [frmRegistration], based on the this table and it includes 7 text fields which need to be checked to ensure data exists in all fields for every record.

I need to filter the recordset to only show only those records containing any blank fields. The fields are named Response1, Response2 ... and so on.

Ultimately, I would like to filter these records using a toggle button on the form so that I can turn the filter on and off again when required.

Would anyone be able to help me accomplish this? I have searched and haven't been able to find anything that works the way I need it to.

Thanks
darls15

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.