Solved

I Need Help with Syntax on this Access Query

Posted on 2015-02-10
7
85 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 142

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 142

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 142

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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