Sql statement to retrieve prior month data using computer date as reference

[Shilabrow]

I need help with sql statement to retrieve prior month data while using the computer date as reference.

select * from table where date BETWEEN #05/01/2014# AND#05/31/2014#);


Instead of putting the prior month date myself in query or using parameter, I need the query to use the computer day to retrieve prior month. So if we are in any day of the month of May, the query should see May and retrieve April 01 to April 30 data and so on for other month. Any help will be appreciated. Thanks

[burrina]

This will get you the current month.

CurMonth: Format(DateSerial(Year(Date()),Month(Date()),Day(D ate())),"mmmm ")

Previous Month:

PrevMonth: Format(DateSerial(Year(Date()),Month(Date())-1,1),"mmmm")

HTH

[Shilabrow]

Thanks for the quick reply, I get data type mismatch in criteria expression when I used either of the two. I actually need to retrieve for previous month only but I get error. All help will be appreciated - Thanks

[burrina]

I don't know why! Did you copy and paste as posted into your query grid?

[Shilabrow]

Yes - for Previous month, I copied and pasted Format(DateSerial(Year(Date()),Month(Date())-1,1),"mmmm") in the criteria box. What am I doing wrong? So now I have

SELECT [Purchase Orders].[Supplier ID], [Purchase Orders].[Creation Date], [Purchase Orders].[Shipping Fee], [Purchase Orders].TaxesFROM [Purchase Orders]
WHERE ((([Purchase Orders].[Creation Date])=Format(DateSerial(Year(Date()),Month(Date()),Day (Date())),"mmmm")));

[burrina]

Creation Date is not going to equal PrevMonth:Format(DateSerial(Year(Date()),Month(Date())-1,1),"mmmm")
Your looking for the value of PrevMonth and use that as your criteria.

[hapm]

How about this:

[Purchase Orders].[Creation Date] BETWEEN DateSerial(Year(Date()),Month(Date())-1,1) AND DateSerial(Year(Date()),Month(Date()),0)

For explenation: DateSerial([Year],[Month],0) will give you the last day of the previous month of [Month].

[Shilabrow]

Thanks hapm. Works like a charm! I appreciate this and thanks for the explanation too.

Burrina - Thanks for your help too!

[Shilabrow]

Hapm, I would appreciate your helping me with this.

I am trying to get data for 3months to date. I have the following but I get error when I used the below query for my date criteria. What am I doing wrong. All help appreciated in helping me retrieve all 3months data to date using computer reference time. Thanks

DateDiff("m\on\th","datecolumn",getdate()<=3)

[axolotl66]

Hi,
I would use this:
DATECOLUMN Between DateDiff("m", -3, Date()) and Date()
Hth
Graham

[Shilabrow]

axolotl66, Thanks but I get a lot more data than needed, It retrieved about 2 year back record rather than just 3months back from today. Could it be because of the Date() at the end. All help appreciated.

[hapm]

Simply change the condition to:

[Purchase Orders].[Creation Date] BETWEEN DateSerial(Year(Date()),Month(Date())-3,1) AND DateSerial(Year(Date()),Month(Date()),0)

[hapm]

Or if you want to have it exactly on the current date, do what axolotl66 suggests but change DateDiff to DateAdd.

[Shilabrow]

Thanks Hapm, I actually want 3month back data from daily date. (April 22 - June 24 todate) or 90 days. Your query worked by retrieving 3 month back to last month of MAy. So I get March, April and May to end data/records. axolot query with the DateAdd gave me error (datatype mismatch). Thanks for your help - so close hapm.

[axolotl66]

My bad, I meant to write dateadd not datediff! I am always doing that! That should add negative 3 months (the same as taking away) for the first argument of the between and then date() is today for the current date.
Hapm's answer is going to be useful for me... Thanks!