Solved

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

Posted on 2016-07-27
17
61 Views
Last Modified: 2016-07-30
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
0
Comment
Question by:darls15
  • 6
  • 4
  • 4
  • +1
17 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41731024
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41731467
[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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41731551
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41731575
Using Nz() only checks for null.  It does not handle ZLS which is usually a possibility with text fields that are in imported files.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41731581
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
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41731824
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41731855
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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41731920
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:PatHartman
ID: 41732032
I didn't have to.  But I did create an example for you to show you that it doesn't.nz2.JPG
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.
0
 

Author Comment

by:darls15
ID: 41732078
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.
0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 125 total points
ID: 41732104
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41732134
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.
0
 

Author Comment

by:darls15
ID: 41732384
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
0
 
LVL 7

Assisted Solution

by:Jonathan Kelly
Jonathan Kelly earned 125 total points
ID: 41732828
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.Form.RecordSource = "qryRegistration_fltr"
To
Me.tblRegistration.Form.RecordSource = "qryRegistration_fltr"

Rgds,
Jonathan
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41733146
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
0
 

Author Comment

by:darls15
ID: 41733977
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!
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41734274
Cheers
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now