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
RexQuality LeaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
Try this:
WHERE Format([Created On],"yyyy-mm") < Format(Date(),"yyyy-mm")
0
RexQuality LeaderAuthor Commented:
IrogSinta:

Same thing. I still returned September data.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What data type is the field?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RexQuality LeaderAuthor Commented:
I just Verified... it's Date/Time
0
mbizupCommented:
Seems like IrogSinta's post should work, but also give this a try:

 WHERE clng([Created On]) < clng(dateserial(Year(date),Month(date),1)
0
mbizupCommented:
If your query is complex, there may be something else preventing this from working.

Can you post the SQL of your query?
0
Rey Obrero (Capricorn1)Commented:
try

where dateserial(year([Created On]), month([Created On]),day([Created On])) < dateserial(year(date()), month(date()),1)
0
RexQuality LeaderAuthor Commented:
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");
0
RexQuality LeaderAuthor Commented:
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
0
mbizupCommented:
Try this:

 WHERE clng([Created On]) < clng(dateserial(Year(date() ),Month(date()),1)
0
mbizupCommented:
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

0
mbizupCommented:
Correcting some ()'s:


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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RexQuality LeaderAuthor Commented:
mbizup

That last one did it! Thank you very much.

Rex
0
RexQuality LeaderAuthor Commented:
thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.