CODEWITHSUNDEEP
ms-accessms-access-2010
user3216329
user3216329

Reputation: 1

What's wrong with this Access query

What's wrong with this Access query. I get no errors, but no records:

There are many records that (seem to) match the criteria.

SELECT Members.[Last Name], Members.[First Name], Members.Address, Members.City, Members.State, Members.Zip, Members.[E-Mail] 
FROM Members 
WHERE (((Members.Inactive)=Yes) AND ((Members.Deceased)<>Yes) AND (IsEmpty(Members.[E-Mail])=Yes)) 
ORDER BY Members.[Last Name], Members.[First Name];

Upvotes: 0

Views: 48

Answers (2)

HansUp
HansUp

Reputation: 97101

IsEmpty(Members.[E-Mail]) will return False for every row.

If you want a condition to select rows where E-Mail is Null, use IsNull([E-Mail]) or [E-Mail] Is Null

If you want a condition to select rows where E-Mail is a zero-length string, use Len([E-Mail]) = 0

If you want a condition to select rows where E-Mail is either Null or a zero-length string, use Len([E-Mail] & '') = 0

Upvotes: 2

JCollerton
JCollerton

Reputation: 3307

Do you want to match Members.Inactive, Members.Deceased etc. to the variable Yes or the string "Yes"? If the latter, then you need to replace:

WHERE 
     (((Members.Inactive)=Yes) 
AND
     ((Members.Deceased)<>Yes) 
AND 
     (IsEmpty(Members.[E-Mail])=Yes))

With:

WHERE 
     (((Members.Inactive)="Yes") 
AND
     ((Members.Deceased)<>"Yes") 
AND 
     (IsEmpty(Members.[E-Mail])="Yes"))

Upvotes: 0

Related Questions