No results.

[pmaxon]

I have a club database and I want to produce a report which contains only those who do not have email addresses. I can produce a report of those with email addresses by using <> &quot; &quot; on the email address field. But I get no results if I use = &quot; &quot; or = &quot;&quot; on the email address field. Any ideas?

TIA!

 

[KenReay]

Hi

The EMail address may be null, try Null in the criteria
Or if you have a mixture of null and spaces for missing EMail addresses, make an additional column in your query:

X:Len(Trim(Nz(EMail,&quot;&quot & &quot;&quot) = 0

and a criteria of true on that column

In cas eyou do not already know, Null is a concept value which means &quot;value unknown&quot; effectively, but it is not equal space and not equal zero - it is a useful concept, but can sometimes cause unexpected results


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bdreed35]

Your email address field may have a NULL value in it.

Try using:

email_addresses Is Not NULL

~Brian

 

[pmaxon]

bdreed35

I think that would give me those with email addresses. I am trying to get those without email address. So far no luck.

 

[bdreed35]

then do the opposite, just remove the NOT :

email_addresses IS NULL

~Brian

 

[pmaxon]

I have tried = NULL and receive no results.

PS - is = NULL different than = &quot;&quot;? They do both give the same results, which is NONE.

 

[bdreed35]

Did you try KenReay's solution above?

Yes, NULL is different than &quot;&quot; (empty string).

~Brian

 

[KenReay]

HI

you could also try

X:Len(Trim(Nz(EMail,&quot;&quot & &quot;&quot) = 0

and a criteria of true on that column

and from first post &quot;In case you do not already know, Null is a concept value which means &quot;value unknown&quot; effectively, but it is not equal space and not equal zero - it is a useful concept, but can sometimes cause unexpected results&quot;


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pmaxon]

Please bear with me here...

are you saying to add a new column &quot;x&quot; in my query and table and place the following criteria on it: Len(Trim(Nz(EMail,&quot;&quot & &quot;&quot) = 0 and if this value is true then it should be a valid &quot;no email address&quot;?

 

[KenReay]

Hi

No, do not add a new column to the table.

In the query make a 'calculated' column (I have called it X, but any name you wish will do)

You do this by typing exactly what I showed you in the area of the query grid where you would normally drag a column name to so type:

X:Len(Trim(Nz(,&quot;&quot & &quot;&quot) = 0

replacing EMail with the name of your column which contains the EMail address

what this bit of code does is to return True if the EMail column is a Null or spaces

in the criteria space of this column put True to pick out those without an EMail address or false to piclout those with an EMail address

OK?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - [URL unfurl="true"]www.kenneth.reay.btinternet.co.uk[/URL]
UK

 

[pmaxon]

Sounds good KenReay -- I'll give it a try!

Thanks a bunch!

 

[pmaxon]

Hey KenReay - it worked flawlessly! Thank you so very much for your help!

 

[KenReay]

Hi

Don't like to gloat, but I did say this in my first post several days ago

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK