After Hours

Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!



Finding empty (and null) data in fields in Access

By ELLB87 ·
Tags: Off Topic
I have a large email database and despite many hours of researching Boolean search criteria and parameters I am unable to accurately create a Query or Table containing all blank/empty email fields. There are two specific factors related to this.

Firstly, we can deal with the null value, which is complex and already described in another TechRepublic forum. This is not that discussion. What's so interesting in my database is that null values only appear when the user did not click into the email box, before leaving it blank. This makes sense as the value of what null actually is. But unfortunately many people tab through the box and therefore I have empty fields that are not technically null and therefore return in queries as not null despite being blank/empty. So I'm unable to use null to search for missing emails.

Secondly, if a person were to search, Like "*@*" in email field, that person would assume to receive a query of all emails. Likewise, logically, a Not Like "*@*" would generate a list of all blank email fields. That logic does not work and usually only half if not less of blank email fields are returned in Query because that criteria negates the null value emails.

Point of the Problem: From what I've gathered, the data is being split two types of blanks, nulls and not nulls that do not contain @. But I'm unable to generate a full list of emails because you cannot ask for Not like "*@*" and Is Null because they are inherent opposites.

Is there anyway to create a query or table that contains both nulls, and a searched criteria or am I going to have to make two queries each time and somehow combine them after for a full list of emails?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums