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 mis...
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
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.
