Forum Discussion
Set up query to find missing data in multiple fields
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
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?
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.
