UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filtering Records, Access 2016    
 
   
JV63
post Jul 18 2019, 02:02 PM
Post#1



Posts: 197
Joined: 16-August 12



Hi all,

Having a block on best way to go about this.

I want to exclude records where the state is PR, type not equal to 3 and if the channel is Mail Service only show CMF that starts with 1. I tried it several ways with AND, OR, Not In, etc.

This is my last attempt and only get Mail Service as output although there are other "channels". I know it's most likely because of the OR but using an AND doesn't work either.


CODE
WHERE (((universe.State)<>"PR") AND ((universe.Type)<>3)) OR (((universe.CMF) Like "1*") AND (([subcat2channel-nsp].Channel)="Mail Service"))


Thanks
Go to the top of the page
 
theDBguy
post Jul 18 2019, 02:04 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,055
Joined: 19-June 07
From: SunnySandyEggo


Hi. Try building the criteria one block at a time and isolate each piece as you add them.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jul 18 2019, 02:15 PM
Post#3



Posts: 197
Joined: 16-August 12



Tried it a couple of ways and I think the problem is that I have records that are not Mail Service that I would want the CMF # regardless of what it started with. It's just the Mail Service where I only want to include those starting with 1.

Trying it now piece at a time and either I get all 1's or just Mail Service and not the rest. Wonder if I need a CASE WHEN for example.
This post has been edited by JV63: Jul 18 2019, 02:16 PM
Go to the top of the page
 
theDBguy
post Jul 18 2019, 02:19 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,055
Joined: 19-June 07
From: SunnySandyEggo


Yes, try a CASE WHEN (if using SQL) or IIF() statement (if using Access) to see if it helps.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jul 18 2019, 02:22 PM
Post#5



Posts: 197
Joined: 16-August 12



Actually looks like I got the desired output by testing it piece meal as you suggested. Just need to test some more.

CODE
WHERE (((universe.State)<>"PR") AND ((universe.Type)<>3) AND (([subcat2channel-nsp].Channel)<>"Mail Service")) OR (((universe.CMF) Like "1*") AND (([subcat2channel-nsp].Channel)="Mail Service") AND ((universe.State)<>"PR") AND ((universe.Type)<>3))

This post has been edited by JV63: Jul 18 2019, 02:26 PM
Go to the top of the page
 
theDBguy
post Jul 18 2019, 02:24 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,055
Joined: 19-June 07
From: SunnySandyEggo


Excellent! Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dale.fye
post Jul 18 2019, 10:19 PM
Post#7



Posts: 137
Joined: 28-March 18
From: Virginia


You might be better off creating the query to select records with those criteria,

WHERE ((universe.State="PR")
AND (universe.Type<>3)
AND (([subcat2channel-nsp].Channel<>"Mail Service") OR (LEFT(universe.CMF,1) = "1")))

and then prefacing the criteria with NOT

WHERE NOT ((universe.State="PR")
AND (universe.Type<>3)
AND (([subcat2channel-nsp].Channel<>"Mail Service") OR (LEFT(universe.CMF,1) = "1")))

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 03:06 AM