Filter in Access

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
99
I would like to retrieve everything in the column but two items ( Pull Pole and Set Pole) Is there a way to do that. I know how to filter on certain items in the Criteria field but, didn't know if there was a way to basically do the opposite.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,426
Office Version
2019
Platform
Windows
In your query, <> "Pull Pole" or <> "Set Pole" in the Field Criteria
 

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
99
I tried it and it gave me a syntax error missing operator in Query expression (Step Job Type). Step Job type is in the field row. I assume I need to add that in the criteria filed somewhere?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
I tried it and it gave me a syntax error missing operator in Query expression (Step Job Type). Step Job type is in the field row. I assume I need to add that in the criteria filed somewhere?
Can you post the query, before you add this criteria, for us?
Just switch to SQL View and copy/paste the code it shows here.

In your query, <> "Pull Pole" or <> "Set Pole" in the Field Criteria
@alansidman
I think you meant:
<> "Pull Pole" And <> "Set Pole"
as "Or" would return every non-blank record.
 

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
99
Here is my Query. not sure where to paste the Criteria in the SQL statement as I am not familiar at all with SQL.

SELECT RAW_COMPLETIONS.District, RAW_COMPLETIONS.subDistrict, RAW_COMPLETIONS.Code, RAW_COMPLETIONS.[Coverage Area], RAW_COMPLETIONS.[Member Name], RAW_COMPLETIONS.[Ticket Number], RAW_COMPLETIONS.[Ticket Type], RAW_COMPLETIONS.[Step Job Type], RAW_COMPLETIONS.[Step Order], RAW_COMPLETIONS.[Step Job Identifier], IIf(Left([RAW_COMPLETIONS]![Step Job Identifier],3)="PWO","PWO",IIf(Right([RAW_COMPLETIONS]![Step Job Identifier],1)="n" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="b" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="a" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="c","EWO","FOK")) AS JOB_TYPE, RAW_COMPLETIONS.[Step Reference], RAW_COMPLETIONS.[Step TaskID], RAW_COMPLETIONS.ServingArea, RAW_COMPLETIONS.[OSPCM Job Status], RAW_COMPLETIONS.[OSPCM Step Status], RAW_COMPLETIONS.[OSPCM Step Estimated Hrs], [JAM DEMAND TOTALS].[OSPCM Step Actual Hrs], RAW_COMPLETIONS.[OSPCM Step Count], RAW_COMPLETIONS.[Ticket House Number], RAW_COMPLETIONS.[Ticket Street Name], RAW_COMPLETIONS.[Created On Dt], RAW_COMPLETIONS.[Created By Member], RAW_COMPLETIONS.[Pole Owner], RAW_COMPLETIONS.ticketRemarks, RAW_COMPLETIONS.stepRemarks, [ticketRemarks] & " // " & [stepremarks] AS allRemarks, RAW_COMPLETIONS.[Step Member Code], RAW_COMPLETIONS.[Next To Go Member], RAW_COMPLETIONS.[Work Requested Dt], RAW_COMPLETIONS.[Next To Go Start Dt], RAW_COMPLETIONS.[Completed Dt], Format([Completed Dt],"ww""Week""") AS [Completed Week], Format([Completed Dt],"mmmm") AS [Completed Month], RAW_COMPLETIONS.[NTG Interval], IIf([NTG Interval]<=30,"<30 Days",IIf([NTG Interval]>30 And [NTG Interval]<=60,"31-60 Days",IIf([NTG Interval]>60 And [NTG Interval]<=240,"61-240 Days",IIf([NTG Interval]>240,">240 Days")))) AS [NTG AGE BUCKET], RAW_COMPLETIONS.Interval, RAW_COMPLETIONS.[Priority Code], RAW_COMPLETIONS.Status, RAW_COMPLETIONS.County, RAW_COMPLETIONS.Place, RAW_COMPLETIONS.[Updated On Dt], RAW_COMPLETIONS.StepLatitude, RAW_COMPLETIONS.StepLongitude, RAW_COMPLETIONS.StepLocation, RAW_COMPLETIONS.ticketLatitude, RAW_COMPLETIONS.ticketLongitude, RAW_COMPLETIONS.ContactName, RAW_COMPLETIONS.ContactPhone, WLS07.[WC Name], WLS07.[Construction Director], WLS07.[Const Area Manager], WLS07.[Const Placing Network Manager]
FROM (RAW_COMPLETIONS LEFT JOIN WLS07 ON RAW_COMPLETIONS.ServingArea = WLS07.CLLI) LEFT JOIN [JAM DEMAND TOTALS] ON RAW_COMPLETIONS.[Step Job Identifier] = [JAM DEMAND TOTALS].[Job NM];
 

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
99
Sorry meant to highlight the Column to post the query.

SELECT RAW_COMPLETIONS.District, RAW_COMPLETIONS.subDistrict, RAW_COMPLETIONS.Code, RAW_COMPLETIONS.[Coverage Area], RAW_COMPLETIONS.[Member Name], RAW_COMPLETIONS.[Ticket Number], RAW_COMPLETIONS.[Ticket Type], RAW_COMPLETIONS.[Step Job Type], RAW_COMPLETIONS.[Step Order], RAW_COMPLETIONS.[Step Job Identifier], IIf(Left([RAW_COMPLETIONS]![Step Job Identifier],3)="PWO","PWO",IIf(Right([RAW_COMPLETIONS]![Step Job Identifier],1)="n" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="b" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="a" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="c","EWO","FOK")) AS JOB_TYPE, RAW_COMPLETIONS.[Step Reference], RAW_COMPLETIONS.[Step TaskID], RAW_COMPLETIONS.ServingArea, RAW_COMPLETIONS.[OSPCM Job Status], RAW_COMPLETIONS.[OSPCM Step Status], RAW_COMPLETIONS.[OSPCM Step Estimated Hrs], [JAM DEMAND TOTALS].[OSPCM Step Actual Hrs], RAW_COMPLETIONS.[OSPCM Step Count], RAW_COMPLETIONS.[Ticket House Number], RAW_COMPLETIONS.[Ticket Street Name], RAW_COMPLETIONS.[Created On Dt], RAW_COMPLETIONS.[Created By Member], RAW_COMPLETIONS.[Pole Owner], RAW_COMPLETIONS.ticketRemarks, RAW_COMPLETIONS.stepRemarks, [ticketRemarks] & " // " & [stepremarks] AS allRemarks, RAW_COMPLETIONS.[Step Member Code], RAW_COMPLETIONS.[Next To Go Member], RAW_COMPLETIONS.[Work Requested Dt], RAW_COMPLETIONS.[Next To Go Start Dt], RAW_COMPLETIONS.[Completed Dt], Format([Completed Dt],"ww""Week""") AS [Completed Week], Format([Completed Dt],"mmmm") AS [Completed Month], RAW_COMPLETIONS.[NTG Interval], IIf([NTG Interval]<=30,"<30 Days",IIf([NTG Interval]>30 And [NTG Interval]<=60,"31-60 Days",IIf([NTG Interval]>60 And [NTG Interval]<=240,"61-240 Days",IIf([NTG Interval]>240,">240 Days")))) AS [NTG AGE BUCKET], RAW_COMPLETIONS.Interval, RAW_COMPLETIONS.[Priority Code], RAW_COMPLETIONS.Status, RAW_COMPLETIONS.County, RAW_COMPLETIONS.Place, RAW_COMPLETIONS.[Updated On Dt], RAW_COMPLETIONS.StepLatitude, RAW_COMPLETIONS.StepLongitude, RAW_COMPLETIONS.StepLocation, RAW_COMPLETIONS.ticketLatitude, RAW_COMPLETIONS.ticketLongitude, RAW_COMPLETIONS.ContactName, RAW_COMPLETIONS.ContactPhone, WLS07.[WC Name], WLS07.[Construction Director], WLS07.[Const Area Manager], WLS07.[Const Placing Network Manager]
FROM (RAW_COMPLETIONS LEFT JOIN WLS07 ON RAW_COMPLETIONS.ServingArea = WLS07.CLLI) LEFT JOIN [JAM DEMAND TOTALS] ON RAW_COMPLETIONS.[Step Job Identifier] = [JAM DEMAND TOTALS].[Job NM];
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
So your updated query with Criteria should look like this:

SELECT RAW_COMPLETIONS.District, RAW_COMPLETIONS.subDistrict, RAW_COMPLETIONS.Code, RAW_COMPLETIONS.[Coverage Area], RAW_COMPLETIONS.[Member Name], RAW_COMPLETIONS.[Ticket Number], RAW_COMPLETIONS.[Ticket Type], RAW_COMPLETIONS.[Step Job Type], RAW_COMPLETIONS.[Step Order], RAW_COMPLETIONS.[Step Job Identifier], IIf(Left([RAW_COMPLETIONS]![Step Job Identifier],3)="PWO","PWO",IIf(Right([RAW_COMPLETIONS]![Step Job Identifier],1)="n" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="b" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="a" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="c","EWO","FOK")) AS JOB_TYPE, RAW_COMPLETIONS.[Step Reference], RAW_COMPLETIONS.[Step TaskID], RAW_COMPLETIONS.ServingArea, RAW_COMPLETIONS.[OSPCM Job Status], RAW_COMPLETIONS.[OSPCM Step Status], RAW_COMPLETIONS.[OSPCM Step Estimated Hrs], [JAM DEMAND TOTALS].[OSPCM Step Actual Hrs], RAW_COMPLETIONS.[OSPCM Step Count], RAW_COMPLETIONS.[Ticket House Number], RAW_COMPLETIONS.[Ticket Street Name], RAW_COMPLETIONS.[Created On Dt], RAW_COMPLETIONS.[Created By Member], RAW_COMPLETIONS.[Pole Owner], RAW_COMPLETIONS.ticketRemarks, RAW_COMPLETIONS.stepRemarks, [ticketRemarks] & " // " & [stepremarks] AS allRemarks, RAW_COMPLETIONS.[Step Member Code], RAW_COMPLETIONS.[Next To Go Member], RAW_COMPLETIONS.[Work Requested Dt], RAW_COMPLETIONS.[Next To Go Start Dt], RAW_COMPLETIONS.[Completed Dt], Format([Completed Dt],"ww""Week""") AS [Completed Week], Format([Completed Dt],"mmmm") AS [Completed Month], RAW_COMPLETIONS.[NTG Interval], IIf([NTG Interval]<=30,"<30 Days",IIf([NTG Interval]>30 And [NTG Interval]<=60,"31-60 Days",IIf([NTG Interval]>60 And [NTG Interval]<=240,"61-240 Days",IIf([NTG Interval]>240,">240 Days")))) AS [NTG AGE BUCKET], RAW_COMPLETIONS.Interval, RAW_COMPLETIONS.[Priority Code], RAW_COMPLETIONS.Status, RAW_COMPLETIONS.County, RAW_COMPLETIONS.Place, RAW_COMPLETIONS.[Updated On Dt], RAW_COMPLETIONS.StepLatitude, RAW_COMPLETIONS.StepLongitude, RAW_COMPLETIONS.StepLocation, RAW_COMPLETIONS.ticketLatitude, RAW_COMPLETIONS.ticketLongitude, RAW_COMPLETIONS.ContactName, RAW_COMPLETIONS.ContactPhone, WLS07.[WC Name], WLS07.[Construction Director], WLS07.[Const Area Manager], WLS07.[Const Placing Network Manager]
FROM (RAW_COMPLETIONS LEFT JOIN WLS07 ON RAW_COMPLETIONS.ServingArea = WLS07.CLLI) LEFT JOIN [JAM DEMAND TOTALS] ON RAW_COMPLETIONS.[Step Job Identifier] = [JAM DEMAND TOTALS].[Job NM] WHERE ((RAW_COMPLETIONS.[Step Job Type]<> "Pull Pole") AND (RAW_COMPLETIONS.[Step Job Type]<>"Set Pole"));
 

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
99
COpy and pasted the entire query and it still gives me the same syntax error
So your updated query with Criteria should look like this:

SELECT RAW_COMPLETIONS.District, RAW_COMPLETIONS.subDistrict, RAW_COMPLETIONS.Code, RAW_COMPLETIONS.[Coverage Area], RAW_COMPLETIONS.[Member Name], RAW_COMPLETIONS.[Ticket Number], RAW_COMPLETIONS.[Ticket Type], RAW_COMPLETIONS.[Step Job Type], RAW_COMPLETIONS.[Step Order], RAW_COMPLETIONS.[Step Job Identifier], IIf(Left([RAW_COMPLETIONS]![Step Job Identifier],3)="PWO","PWO",IIf(Right([RAW_COMPLETIONS]![Step Job Identifier],1)="n" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="b" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="a" Or Right([RAW_COMPLETIONS]![Step Job Identifier],1)="c","EWO","FOK")) AS JOB_TYPE, RAW_COMPLETIONS.[Step Reference], RAW_COMPLETIONS.[Step TaskID], RAW_COMPLETIONS.ServingArea, RAW_COMPLETIONS.[OSPCM Job Status], RAW_COMPLETIONS.[OSPCM Step Status], RAW_COMPLETIONS.[OSPCM Step Estimated Hrs], [JAM DEMAND TOTALS].[OSPCM Step Actual Hrs], RAW_COMPLETIONS.[OSPCM Step Count], RAW_COMPLETIONS.[Ticket House Number], RAW_COMPLETIONS.[Ticket Street Name], RAW_COMPLETIONS.[Created On Dt], RAW_COMPLETIONS.[Created By Member], RAW_COMPLETIONS.[Pole Owner], RAW_COMPLETIONS.ticketRemarks, RAW_COMPLETIONS.stepRemarks, [ticketRemarks] & " // " & [stepremarks] AS allRemarks, RAW_COMPLETIONS.[Step Member Code], RAW_COMPLETIONS.[Next To Go Member], RAW_COMPLETIONS.[Work Requested Dt], RAW_COMPLETIONS.[Next To Go Start Dt], RAW_COMPLETIONS.[Completed Dt], Format([Completed Dt],"ww""Week""") AS [Completed Week], Format([Completed Dt],"mmmm") AS [Completed Month], RAW_COMPLETIONS.[NTG Interval], IIf([NTG Interval]<=30,"<30 Days",IIf([NTG Interval]>30 And [NTG Interval]<=60,"31-60 Days",IIf([NTG Interval]>60 And [NTG Interval]<=240,"61-240 Days",IIf([NTG Interval]>240,">240 Days")))) AS [NTG AGE BUCKET], RAW_COMPLETIONS.Interval, RAW_COMPLETIONS.[Priority Code], RAW_COMPLETIONS.Status, RAW_COMPLETIONS.County, RAW_COMPLETIONS.Place, RAW_COMPLETIONS.[Updated On Dt], RAW_COMPLETIONS.StepLatitude, RAW_COMPLETIONS.StepLongitude, RAW_COMPLETIONS.StepLocation, RAW_COMPLETIONS.ticketLatitude, RAW_COMPLETIONS.ticketLongitude, RAW_COMPLETIONS.ContactName, RAW_COMPLETIONS.ContactPhone, WLS07.[WC Name], WLS07.[Construction Director], WLS07.[Const Area Manager], WLS07.[Const Placing Network Manager]
FROM (RAW_COMPLETIONS LEFT JOIN WLS07 ON RAW_COMPLETIONS.ServingArea = WLS07.CLLI) LEFT JOIN [JAM DEMAND TOTALS] ON RAW_COMPLETIONS.[Step Job Identifier] = [JAM DEMAND TOTALS].[Job NM] WHERE ((RAW_COMPLETIONS.[Step Job Type]<> "Pull Pole") AND (RAW_COMPLETIONS.[Step Job Type]<>"Set Pole"));

Looks like everything is working fine now. Not sure what it was but I copied and pasted again and it seems fine now. Thanks for all the great help. I really appreciate it.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
You are welcome.

If you switch the query back to Query View, you can see how you would create that criteria using the Query Builder.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,733
Messages
5,446,188
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top