Filter in Access

[bwlytkr]

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.

 

[alansidman]

In your query, <> "Pull Pole" or <> "Set Pole" in the Field Criteria

 

[bwlytkr]

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]

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]

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]

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]

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]

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]

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.