Improve company productivity with a Business Account.Sign Up

x
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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