Excluding empty fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

emc - 23 Apr 2008 10:44 GMT

I would like to run a query by excluding the fields that have no data. There
are three fields Email, Email2 and Email3. Some people have none, one, two
or three email address. How can I run a query to show only the people who
have email address and exclude the ones who have nothing at all?

Any help is greatly appreciated.

emc

Reply to this Message

Jeanette Cunningham - 23 Apr 2008 11:15 GMT

emc,
you can create a calculated field in the query that concatenates the 3 email
addresses into one field and find out where the 3 addresses combined is an
empty string.

In a new column in the query type the following in the Field row
Expr1: Len([email1] & [email2] & [email3])

In the criteria row for this column type

Run the query and records with no emails at all are excluded.

Jeanette Cunningham

>I would like to run a query by excluding the fields that have no data.
>There
[quoted text clipped - 6 lines]
>
> emc

Reply to this Message

emc - 23 Apr 2008 11:25 GMT

Jeanette,

This has worked a treat. Many thanks.

One thing I have noticed is that in the Expr1 field there are numbers. Do
you know what they are?

emc

> emc,
> you can create a calculated field in the query that concatenates the 3 email
[quoted text clipped - 21 lines]
> >
> > emc

Reply to this Message

Jeanette Cunningham - 23 Apr 2008 11:27 GMT

emc,
the numbers come from the Len part of expr1
Len is a function that counts how many characters in a string and shows the
count as a number.

Jeanette Cunningham

> Jeanette,
>
[quoted text clipped - 33 lines]
>> >
>> > emc

Reply to this Message

emc - 23 Apr 2008 11:36 GMT

Thank you.

> emc,
> the numbers come from the Len part of expr1
[quoted text clipped - 40 lines]
> >> >
> >> > emc

Reply to this Message