Query to return Current and Previous Month from a Date Field??

[paulcrigh]

Hi All,

As I am suggesting in my title, I am trying to export from a single table only the current and previous months sales transactions based upon a date field dd/mm/yyyy.

Exporting only current and previous months is straight forward, however if like this month we are in period 1, the current and previous script I have written is not clever enough to export January 2010 and the previous month of December 2009.

My experience is in SQL scripting so the script I have done has been done in the SQL View in Access Query.

Can anyone help with my below script please so it knows if we are in period 1 that the previous month is period 12 of the previous year please?? I think this is tricky but any help would be much appreciated:

My script to export current and previous is:

SELECT Customers.CustCode, Items.ItemCode, Inv_Line.ILineQty, Inv_Line.ILineLineTot, Inv_Line.IlineItemCost, Cstr([Inv_Line].[ILineQty]*[Inv_Line].[IlineItemCost]) AS TotalCost, Invoices.InDate

FROM UsersNew INNER JOIN ((Reps INNER JOIN Customers ON Reps.[ID] = Customers.[CustRepID]) INNER JOIN (Invoices INNER JOIN (Items INNER JOIN Inv_Line ON Items.[ItemID] = Inv_Line.[ILineItemCodeID]) ON Invoices.[InID] = Inv_Line.[IlineInvNoID]) ON Customers.[CustID] = Inv_Line.[ILineAccountID]) ON UsersNew.ID = Invoices.InUserID

WHERE Year([Invoices].[InDate]) = Year(Now())
AND (Month([Invoices].[InDate]) = Month(Now())
OR Month([Invoices].[InDate]) = Month(Now())-1);

Thanks

Paul

 

[Brianwarnock]

Use Dateserial

Where Indate between dateserial(year(date()),month(date())-1,1) and Date()

There is a thread in the code repository covering such issues.

Brian

 

[paulcrigh]

Hi Brian,

Thanks ever so much for the pointer. To be totally honest because I was going into it blind and only knowing SQL and minimal Access querying I used everything including Datediff, IIF statements, CASe statements but not once did i try the DateSerial so thanks for the pointer

Hopefully now I can work out the code but im sure its not overly complicated if the repository has examples

thanks again

Paul

 

[Brianwarnock]

Erm! I gave you the code for your where clause, but please read the thread in the code repository.

Brian

 

[paulcrigh]

Apologies Brian - You did indeed give me the where clause.

Thank you very much for your help

Paul