• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

I Need Help with Syntax on this Access Query

My Query is shown below... Access keeps autobracketing the "Text" items in the NOT Statements when i save the query, and then prompts me to ENTER A PARAMETER VALUE for each as the query executes. Can someone please help me get this functional?

SELECT Format([Created On],"yyyy-mm") AS MonthandYear, Sum(KO_QN_Data.[DefectQty (ext)]) AS Total_QNs INTO Barry_Monthly_KO_QN_Total
FROM KO_QN_Data
WHERE KO_QN_Data.[Short text for code]="Kimball Office Furniture"
AND ( GetBucket([Code group] ) ="Product" OR GetBucket([Code group]) ="Delivery")
AND clng([Created On]) < clng(dateserial(Year(date() ),Month(date()),1))
AND NOT [Code Group] = “ZVOID”
AND NOT [Code Group] = “PRODSUGG”
AND NOT [Code Group] = “C&CERROR”
AND NOT [Code Group] = “RAWMATL”
AND NOT [Code Group] IS NULL
GROUP BY Format([Created On],"yyyy-mm")
ORDER BY Format([Created On],"yyyy-mm");
0
Rex85
Asked:
Rex85
  • 4
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
“  is not the same as "  maybe you just put the "wrong" double quotes?
0
 
Rex85Author Commented:
No... I just tried changing them. Access changes them back when i save it and does the same thing
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Please try this:

AND [Code Group] NOT IN ( "ZVOID", "PRODSUGG", "C&CERROR", "RAWMATL")

the same way, you may want to change (for performance) this:
AND ( GetBucket([Code group] ) ="Product" OR GetBucket([Code group]) ="Delivery")
into this
AND ( GetBucket([Code group] )  IN ( "Product" , "Delivery") )
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rex85Author Commented:
OK. Great... That worked. Thank you.

Is there a way i can incorporate the Null avoidance in that? ...or is it OK as is?

I currently have...

AND NOT [Code Group] IS NULL
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot incorporate that into the IN or NOT IN, however:
AND NOT [Code Group] IS NULL

should be
AND [Code Group] IS NOT NULL
0
 
Rex85Author Commented:
Fantastic! Thank you very much.

Rex
0
 
Rex85Author Commented:
Great. Very Quick and concise. Thank you.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now