Avatar of Rex
RexFlag for United States of America asked on

I Need to Exclude Current month Data from Query Returns

Hello:

I am trying to do something like...


WHERE Format([Created On],"yyyy-mm") < Date()

...to only return data that excludes the current month. My dates are individual mm/dd/yyyy dates, I use the Format approach to put them in a Monthly format, so they can be grouped by month. Since the current month is never complete, I only want the last complete month, which is always "Last month"

This statement seems to be doing nothing. I return September data whether I include the staement or not. I have tried several (over 10) ways of tweaking this, but can't get it to exclude current month data from the returns.

Can anyone tell me how to do this please?

Rex
Microsoft AccessSQL

Avatar of undefined
Last Comment
Rex

8/22/2022 - Mon
IrogSinta

Try this:
WHERE Format([Created On],"yyyy-mm") < Format(Date(),"yyyy-mm")
ASKER
Rex

IrogSinta:

Same thing. I still returned September data.
Guy Hengel [angelIII / a3]

What data type is the field?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Rex

I just Verified... it's Date/Time
mbizup

Seems like IrogSinta's post should work, but also give this a try:

 WHERE clng([Created On]) < clng(dateserial(Year(date),Month(date),1)
mbizup

If your query is complex, there may be something else preventing this from working.

Can you post the SQL of your query?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rey Obrero (Capricorn1)

try

where dateserial(year([Created On]), month([Created On]),day([Created On])) < dateserial(year(date()), month(date()),1)
ASKER
Rex

SQL (With Capricorn1 addition, since i tried it last)

SELECT Format([Created On],"yyyy-mm") AS MonthandYear, Sum(KO_QN_Data.[DefectQty (ext)]) AS Total_QNs INTO KO_QN_EFR_AA_Product_Plus_Delivery_Sum_Monthly_Table
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


dateserial(year([Created On]), month([Created On]),day([Created On])) < dateserial(year(date()), month(date()),1)


GROUP BY Format([Created On],"yyyy-mm")
ORDER BY Format([Created On],"yyyy-mm");
ASKER
Rex

mbizup:

When I tried yours, it prompted for a value for "date", then gave me a Data Type Mismatch Error

Capricorn1

I tried yours, but it still returned September data
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
mbizup

Try this:

 WHERE clng([Created On]) < clng(dateserial(Year(date() ),Month(date()),1)
mbizup

In your query...

SELECT Format([Created On],"yyyy-mm") AS MonthandYear, Sum(KO_QN_Data.[DefectQty (ext)]) AS Total_QNs INTO KO_QN_EFR_AA_Product_Plus_Delivery_Sum_Monthly_Table
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)
GROUP BY Format([Created On],"yyyy-mm")
ORDER BY Format([Created On],"yyyy-mm");

Open in new window

ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Rex

mbizup

That last one did it! Thank you very much.

Rex
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Rex

thank you!