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. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
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
-
two pages side by side land scape
by
marc
1 hour, 1 minute ago -
Deleting obsolete OneNote notebooks
by
afillat
3 hours, 6 minutes ago -
Word/Outlook 2024 vs Dragon Professional 16
by
Kathy Stevens
3 hours, 17 minutes ago -
Security Essentials or Defender?
by
MalcolmP
3 hours, 20 minutes ago -
April 2025 updates out
by
Susan Bradley
1 hour, 24 minutes ago -
Framework to stop selling some PCs in the US due to new tariffs
by
Alex5723
5 hours, 32 minutes ago -
WARNING about Nvidia driver version 572.83 and 4000/5000 series cards
by
Bob99
5 hours, 34 minutes ago -
Creating an Index in Word 365
by
CWBillow
17 hours, 1 minute ago -
Coming at Word 365 and Table of Contents
by
CWBillow
17 hours, 6 minutes ago -
Windows 11 Insider Preview Build 22635.5170 (23H2) released to BETA
by
joep517
22 hours, 2 minutes ago -
Has the Microsoft Account Sharing Problem Been Fixed?
by
jknauth
1 day, 1 hour ago -
W11 24H2 – Susan Bradley
by
G Pickerell
1 day, 3 hours ago -
7 tips to get the most out of Windows 11
by
Alex5723
1 day, 1 hour ago -
Using Office apps with non-Microsoft cloud services
by
Peter Deegan
18 hours, 46 minutes ago -
I installed Windows 11 24H2
by
Will Fastie
7 hours, 26 minutes ago -
NotifyIcons — Put that System tray to work!
by
Deanna McElveen
1 day, 6 hours ago -
Decisions to be made before moving to Windows 11
by
Susan Bradley
1 hour, 19 minutes ago -
Port of Seattle says ransomware breach impacts 90,000 people
by
Nibbled To Death By Ducks
1 day, 15 hours ago -
Looking for personal finance software with budgeting capabilities
by
cellsee6
23 hours, 18 minutes ago -
ATT/Yahoo Secure Mail Key
by
Lil88reb
23 hours, 32 minutes ago -
Devices with apps using sprotect.sys driver might stop responding
by
Alex5723
2 days, 8 hours ago -
Neowin – 20 times computers embarrassed themselves with public BSODs and goofups
by
EP
2 days, 16 hours ago -
Slow Down in Windows 10 performance after March 2025 updates ??
by
arbrich
1 day, 18 hours ago -
Mail from certain domains not delivered to my outlook.com address
by
pumphouse
2 days, 1 hour ago -
Is data that is in OneDrive also taking up space on my computer?
by
WShollis1818
2 days, 11 hours ago -
Nvidia just fixed an AMD Linux bug
by
Alex5723
4 days, 3 hours ago -
50 years and counting
by
Susan Bradley
1 day, 1 hour ago -
Fix Bluetooth Device Failed to Delete in Windows Settings
by
Drcard:))
1 day, 4 hours ago -
Licensing and pricing updates for on-premises server products coming July 2025
by
Alex5723
4 days, 14 hours ago -
Edge : Deprecating window.external.getHostEnvironmentValue()
by
Alex5723
4 days, 14 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.