Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Filter records in a forms where one or more fields are blank
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
Who is Participating?
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
[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.
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
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
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
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.
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
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.
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.
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
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
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
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!
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
ResponseDBUpdated.accdb