I want to filter out records without an email address on a specific criteria of classification. The classification works fine but I also get both null and not null records.
![]() |
Patch reliability is unclear, but widespread attacks make patching prudent. Go ahead and patch, but watch out for potential problems. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
ACCESS 2007 Null and criteria query
Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » ACCESS 2007 Null and criteria query
- This topic has 8 replies, 3 voices, and was last updated 8 years, 2 months ago.
AuthorTopicGoatfarmer98
AskWoody LoungerFebruary 3, 2017 at 12:40 pm #508116Viewing 2 reply threadsAuthorReplies-
RetiredGeek
AskWoody_MVPFebruary 3, 2017 at 12:55 pm #1590420G.F.
Is it correct that you have a field called Classification and you want to ONLY filter out records with a blank email address for a specific value of the Classification field?
If that is the case you need a two row query grid.
On the first row you’ll set the Classification field formula to The excluded classification value
On the second row you’ll set the Classification field formula to = The excluded classification value and the Email field to “”Of course you include all other fields the same in both lines.
Should do the trick as this is an OR query and will select records that conform to either of the two rows.HTH :cheers:
-
Goatfarmer98
AskWoody LoungerFebruary 3, 2017 at 2:12 pm #1590424Actually Mr. Retired, a beer right now sounds mighty good!
My query contains the 2 fields “Email1” and “Citizen/Leg” I need to work with.
I want only the fields with an Email address and those records with “SFL”, or “YAL” or “C” or “F” in the “citizen/leg” field.
Citizen/leg is the first field in the query immediately followed by Email1.
I get the “SFL” and the other 3 criteria records when I have no Email1 criteria, alone with blank and filled Email1 fields (image retired1.)
However, when I put “not null” on the first criteria row of Email1 and beginning with “SFL” on the second row of Citizen/Leg criteria, followed beneath it the remaining 3 other criteria on subsequent rows, I get the other files beneath Citizen/leg including my “H”ouse and “S”enate legislators’ records in database view and all of the emails – with or without data.(image retired.2)
Thank you very much for your patience. You wouldn’t have to twist my arm to bring us a couple of beers out of Auburn, Alabama area.
46619-retired.146620-retired.2
Either field at this time is “sorted” and both checked to “show.”
-
Goatfarmer98
AskWoody Lounger
-
RetiredGeek
AskWoody_MVPFebruary 3, 2017 at 3:50 pm #1590426G.F.,
Ok here’s where I think your problem is. The first line in your query will match All Citizen/Leg values! So they are all returned.
You need to move one of the values probably “F” since that will be the easiest to get rid of in the list. Next you need to repeat the IS NOT NULL on each of the rows.
Access evaluates each of the criteria rows only by the criteria that exist on that row.
HTH :cheers:
-
Goatfarmer98
AskWoody LoungerFebruary 3, 2017 at 4:19 pm #1590427You rock Mr. Retired. How do I acknowledge your solution with a “star” or “1” as you are? Will now have that beer. Might have two, one for the each of us. Thank you VERY much.
G.F.,
Ok here’s where I think your problem is. The first line in your query will match All Citizen/Leg values! So they are all returned.
You need to move one of the values probably “F” since that will be the easiest to get rid of in the list. Next you need to repeat the IS NOT NULL on each of the rows.
Access evaluates each of the criteria rows only by the criteria that exist on that row.
HTH :cheers:
-
RetiredGeek
AskWoody_MVPFebruary 3, 2017 at 7:43 pm #1590430You rock Mr. Retired. How do I acknowledge your solution with a “star” or “1” as you are?
Just click the thumbs up icon on the post that helped you, it’s located at the bottom left of the post.
Glad to have been of assistance.
:cheers:
-
Goatfarmer98
AskWoody Lounger
-
-
WSMarkLiquorman
AskWoody LoungerFebruary 3, 2017 at 9:47 pm #1590432As I see it, the WHERE clause of your SQL should be:
WHERE [Email1] Is Not Null AND [Citizen/Leg] IN (“SFL”, “YAL”, “C”,”F”)This says “I want to return all records that have an email value and that have 1 of 4 acceptable values for citizen/leg”. This assumes that the Email1 field can not contain a zero-length string. If it can, then use: WHERE [Email1] & “” “” AND …
On a separate issue, you should get out of the habit of using special characters in your fieldnames, with the exception of the underscore character.
Viewing 2 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Solid color background slows Windows 7 login
by
Alex5723
1 hour, 3 minutes ago -
Windows 11, version 24H2 might not download via Windows Server Updates Services
by
Alex5723
1 hour, 31 minutes ago -
Security fixes for Firefox
by
Susan Bradley
3 hours, 7 minutes ago -
Notice on termination of services of LG Mobile Phone Software Updates
by
Alex5723
2 hours, 54 minutes ago -
Update your Apple Devices Wormable Zero-Click Remote Code Execution in AirPlay..
by
Alex5723
12 hours ago -
Amazon denies it had plans to be clear about consumer tariff costs
by
Alex5723
2 hours, 59 minutes ago -
Return of the brain dead FF sidebar
by
EricB
9 hours, 1 minute ago -
windows settings managed by your organization
by
WSDavidO61
13 hours, 6 minutes ago -
Securing Laptop for Trustee Administrattor
by
PeachesP
26 minutes ago -
The local account tax
by
Susan Bradley
4 hours, 40 minutes ago -
Recall is back with KB5055627(OS Build 26100.3915) Preview
by
Alex5723
1 day, 1 hour ago -
Digital TV Antenna Recommendation
by
Win7and10
17 hours, 32 minutes ago -
Server 2019 Domain Controllers broken by updates
by
MP Support
1 day, 12 hours ago -
Google won’t remove 3rd party cookies in Chrome as promised
by
Alex5723
1 day, 14 hours ago -
Microsoft Manager Says macOS Is Better Than Windows 11
by
Alex5723
1 day, 17 hours ago -
Outlook (NEW) Getting really Pushy
by
RetiredGeek
20 hours, 10 minutes ago -
Steps to take before updating to 24H2
by
Susan Bradley
10 hours, 59 minutes ago -
Which Web browser is the most secure for 2025?
by
B. Livingston
1 day ago -
Replacing Skype
by
Peter Deegan
13 hours, 9 minutes ago -
FileOptimizer — Over 90 tools working together to squish your files
by
Deanna McElveen
1 day, 11 hours ago -
Excel Macro — ask for filename to be saved
by
nhsj
9 hours, 6 minutes ago -
Trying to backup Win 10 computer to iCloud
by
SheltieMom
12 hours, 55 minutes ago -
Windows 11 Insider Preview build 26200.5570 released to DEV
by
joep517
3 days, 17 hours ago -
Windows 11 Insider Preview build 26120.3941 (24H2) released to BETA
by
joep517
3 days, 19 hours ago -
Windows 11 Insider Preview Build 22635.5305 (23H2) released to BETA
by
joep517
3 days, 19 hours ago -
No April cumulative update for Win 11 23H2?
by
Peobody
2 days, 7 hours ago -
AugLoop.All (TEST Augmentation Loop MSIT)
by
LarryK
3 days, 20 hours ago -
Boot Sequence for Dell Optiplex 7070 Tower
by
Serge Carniol
4 days, 11 hours ago -
OTT Upgrade Windows 11 to 24H2 on Unsupported Hardware
by
bbearren
5 hours, 3 minutes ago -
Inetpub can be tricked
by
Susan Bradley
2 days, 22 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.