SOLVED

Set up query to find missing data in multiple fields

%3CLINGO-SUB%20id%3D%22lingo-sub-2461288%22%20slang%3D%22en-US%22%3ESet%20up%20query%20to%20find%20missing%20data%20in%20multiple%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2461288%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20Microsoft%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20set%20up%20a%20query%20that%20will%20find%20records%20that%20are%20missing%20data.%26nbsp%3B%20For%20example%2C%20I%20have%20a%20main%20form%20where%20users%20will%20enter%20biographical%20data%2C%20but%20they%20may%20be%20missing%20the%20birthday%2C%20the%20last%20name%2C%20and%20the%20phone%20number.%26nbsp%3B%20The%20idea%20is%20that%20this%20query%20will%20help%20us%20identify%20the%20records%20which%20are%20missing%20data%20and%20allow%20us%20to%20then%20go%20back%20and%20enter%20this%20data%20in%20when%20we%20find%20it.%26nbsp%3B%20How%20would%20I%20set%20up%20the%20query%20to%20catch%20the%20records%20with%20any%20missing%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20first%20tried%20to%20place%20%22Is%20Null%22%20(not%20in%20quotes)%20in%20the%20OR%20section%20of%20the%20query%2C%20and%20it%20worked%20with%20only%20one%20field%20being%20tested%2C%20but%20as%20I%20added%20more%20fields%20it%20could%20not%20find%20a%20record%20with%20missing%20data.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20always%20thank%20you%20for%20taking%20the%20time%20to%20read%20this%20and%20help%20with%20this%20issue.%26nbsp%3B%20I%20look%20forward%20to%20your%20response.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3EEb%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2461288%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMissing%20data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2461310%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20up%20query%20to%20find%20missing%20data%20in%20multiple%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2461310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1069909%22%20target%3D%22_blank%22%3E%40ebenton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20could%20be%20more%20precise%20with%20more%20details%20to%20work%20with%2C%20but%20the%20general%20pattern%20would%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20FirstName%2C%20LastName%2C%20DateofBirth%2C%20PhoneNumber%3C%2FP%3E%3CP%3EFROM%20tblYourTableNameGoesHere%3C%2FP%3E%3CP%3EWHERE%20FirstName%20Is%20Null%20OR%20LastName%20Is%20Null%20OR%20DateOfBirth%20Is%20Null%20OR%20PhoneNumber%20Is%20Null%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20it%20is%20possible%20that%20one%20or%20more%20of%20them%20is%20actually%20a%20Zero%20Length%20String%20(ZLS)%20so%20I'd%20write%20that%20to%20cover%20that%20possibility%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20FirstName%2C%20LastName%2C%20DateofBirth%2C%20PhoneNumber%3C%2FP%3E%3CP%3EFROM%20tblYourTableNameGoesHere%3C%2FP%3E%3CP%3EWHERE%20Len(FirstName%20%26amp%3B%20%22%22)%20%3D%200%20OR%20Len(LastName%20%26amp%3B%20%22%22)%20%3D%200%20OR%20DateOfBirth%20Is%20Null%20OR%20Len(PhoneNumber%20%26amp%3B%20%22%22)%20%3D%200%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20date%20field%20will%20be%20null%20or%20have%20a%20valid%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20that%20what%20you%20tried%20before%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2462440%22%20slang%3D%22en-US%22%3ERe%3A%20Set%20up%20query%20to%20find%20missing%20data%20in%20multiple%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2462440%22%20slang%3D%22en-US%22%3EGeorge%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20think%20the%20problem%20was%20that%20I%20wasn't%20catching%20the%20strings%20with%20zero%20length%20as%20you%20mentioned.%20Using%20your%20advice%2C%20I%20changed%20the%20string%20catchers%20to%20Len(fieldname%20%26amp%3B%20%22%22)%20%3D%200%20and%20it%20successfully%20caught%20the%20records.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20so%20much%20for%20taking%20the%20time%20to%20respond%20and%20for%20fixing%20the%20problem.%3CBR%20%2F%3E%3CBR%20%2F%3ESincerely%2C%3CBR%20%2F%3EEb%3C%2FLINGO-BODY%3E
Occasional Contributor

Hey Microsoft Community,

 

I am trying to set up a query that will find records that are missing data.  For example, I have a main form where users will enter biographical data, but they may be missing the birthday, the last name, and the phone number.  The idea is that this query will help us identify the records which are missing data and allow us to then go back and enter this data in when we find it.  How would I set up the query to catch the records with any missing data?

 

I first tried to place "Is Null" (not in quotes) in the OR section of the query, and it worked with only one field being tested, but as I added more fields it could not find a record with missing data.  

 

As always thank you for taking the time to read this and help with this issue.  I look forward to your response.  

 

Thanks, 

Eb

4 Replies
best response confirmed by ebenton (Occasional Contributor)
Solution

@ebenton 

We could be more precise with more details to work with, but the general pattern would be:

 

SELECT FirstName, LastName, DateofBirth, PhoneNumber

FROM tblYourTableNameGoesHere

WHERE FirstName Is Null OR LastName Is Null OR DateOfBirth Is Null OR PhoneNumber Is Null

 

However, it is possible that one or more of them is actually a Zero Length String (ZLS) so I'd write that to cover that possibility:

 

SELECT FirstName, LastName, DateofBirth, PhoneNumber

FROM tblYourTableNameGoesHere

WHERE Len(FirstName & "") = 0 OR Len(LastName & "") = 0 OR DateOfBirth Is Null OR Len(PhoneNumber & "") = 0

 

The date field will be null or have a valid date.

 

Is that what you tried before? 

 

 

George,

I think the problem was that I wasn't catching the strings with zero length as you mentioned. Using your advice, I changed the string catchers to Len(fieldname & "") = 0 and it successfully caught the records.

Thank you so much for taking the time to respond and for fixing the problem.

Sincerely,
Eb
Also for anyone else struggling with this problem, when I checked the design view of the query, the Is Null has to be on ascending lines. Originally, I had kept the Is Null on the same line of the OR section. When I entered in the SQL statement that George prescribed, the "Is Null" and Len(fieldname & "") = 0 all appeared on separate lines. So I am assuming that this is necessary for it to work properly. Hope this helps anyone else.
Thank you for including that detail. And yes, it is an artifact of the way the Query Design Grid has to work to accommodate the underlying SQL. Showing the options on a single line visually tells Access to use "AND" between those options. Showing the options on different lines visually tells Access to use "OR" between those options. I prefer to change over and work in the SQL designer rather than the Design Grid because it is closer to what the database engine is actually going to do, but the grid is handy for simpler queries and for getting things off to a quick start.