Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.
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
Active today
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.
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.
Active today
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
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.
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.
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
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.
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
Active today
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
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
Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…
Suggested Courses
Course of the Month8 days, 9 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.