We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
10 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Filter records in a forms where one or more fields are blank
Posted on 2016-07-27
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6
4-
4
- +1
17 Comments
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.Fo
Me.Requery
Else
Me.togSrc.Caption = "Click To Set Filter"
Me.frmRegistration_DATA.Fo
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
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.Fo
Me.Requery
Else
Me.togSrc.Caption = "Click To Set Filter"
Me.frmRegistration_DATA.Fo
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
Active today
[Response1] = NULL AND [Response2] = NULLYou 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.
Active 5 days ago
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
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
Active today
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
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
Active 5 days ago
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
<<<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
Active today
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.
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.
Active 5 days ago
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
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
Active today
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.
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.
Active 5 days ago
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.
Active 5 days ago
I tested it with a ZLS and it worked.
Unless I am missing something this is how I've always understood ZLS to work:
You can check for ZLS by looking for "" - an empty string. You can check for NULL by using the IsNull function. In your VBA code this will often result in looking for both:
If IsNull(LastName) or LastName="" Then
' Do some stuff
End if
Therefore, this method simply test for both!
Nz(Response, "") =""
ET
Unless I am missing something this is how I've always understood ZLS to work:
You can check for ZLS by looking for "" - an empty string. You can check for NULL by using the IsNull function. In your VBA code this will often result in looking for both:
If IsNull(LastName) or LastName="" Then
' Do some stuff
End if
Therefore, this method simply test for both!
Nz(Response, "") =""
ET
Active today
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.
Active 5 days ago
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
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
darls15,
The button you have added needs to be a toggle control rather than a command button.
Remove the existing button and add a new toggle button.
Add the code to the on_click event.
Also you need to reference the subform by name - it looks like you have added the table as the subform so you need to
Change
Me.frmRegistration_DATA.Fo
To
Me.tblRegistration.Form.Re
Rgds,
Jonathan
The button you have added needs to be a toggle control rather than a command button.
Remove the existing button and add a new toggle button.
Add the code to the on_click event.
Also you need to reference the subform by name - it looks like you have added the table as the subform so you need to
Change
Me.frmRegistration_DATA.Fo
To
Me.tblRegistration.Form.Re
Rgds,
Jonathan
Active today
I modified the code so it would work. buttons don't have a value so your code didn't compile. I changed it to look at the caption to make the decision. I also loaded the default caption in the form's open event. I removed the recordsource from the main form since there is no need for it and cleaned up a few property settings.
ResponseDBUpdated.accdb
ResponseDBUpdated.accdb
Active 5 days ago
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!
Featured Post
Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.
But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string.
Specify the first argument, which is the expression to be returned:
Specify the second argument, which …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 21 hours left to enroll
631 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.