Finding records w/empty field

  • Thread starter Thread starter Thomas Hamlett
  • Start date Start date
T

Thomas Hamlett

Know this is easy but I want to find all the records that have no emails in
the Email field
 
Thomas

Create a new query in design view.

Add the table. Add the fields.

If the email field contains nothing, put 'Is Null' (without the quotes) in
the Selection Criterion under the email field.

If the email field could contain zero-length strings (these look like "no
emails"), use '<> ""' (that's two quotes without space).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Put this in the criteria for the Email field in a query.

Is Null Or "" Or Like " *"

You actually need to check for 3 things to be sure. In a perfect world, you
would only have to check for the first one which is Nulls.

However it's possible to have something called a Zero Length String. This
most often happens if you import data from other data sources.

The last one is looking for just spaces in the field. It may look empty, but
spaces can be there.

I just thought of another way that would work in your situation. A valid
email address needs an @ symbol. Something like below would not only show
empty fields, but also email addresses which aren't valid!

SELECT [Employee Addresses].[E-Mail],
InStr([E-Mail],"@") AS Expr1
FROM [Employee Addresses]
WHERE InStr([E-Mail],"@")<1 ;

InStr([E-Mail],"@") would go in the Field of a query and the <1 would be in
the criteria.
 
Back
Top