Solved

I Need Help with Syntax on this Access Query

Posted on 2015-02-10
7
86 Views
Last Modified: 2015-02-10
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
Comment
Question by:Rex85
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40600656
“  is not the same as "  maybe you just put the "wrong" double quotes?
0
 

Author Comment

by:Rex85
ID: 40600665
No... I just tried changing them. Access changes them back when i save it and does the same thing
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40600680
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Rex85
ID: 40600693
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40600698
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
 

Author Comment

by:Rex85
ID: 40600703
Fantastic! Thank you very much.

Rex
0
 

Author Closing Comment

by:Rex85
ID: 40600709
Great. Very Quick and concise. Thank you.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question