Link to home
Start Free TrialLog in
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
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

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

ASKER

IrogSinta:

Same thing. I still returned September data.
What data type is the field?
Avatar of Rex

ASKER

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

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

Can you post the SQL of your query?
try

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

ASKER

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");
Avatar of Rex

ASKER

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
Try this:

 WHERE clng([Created On]) < clng(dateserial(Year(date() ),Month(date()),1)
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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rex

ASKER

mbizup

That last one did it! Thank you very much.

Rex
Avatar of Rex

ASKER

thank you!