Last Month

[jonnymenthol]

Hello,

I have a query which pulls data based on a date range.
However, instead of using this, I'd like to only get data for last month.

I have tried doing this by adding 2 fields :

Month: Month(myDate)

and

Year : Year(myDate)

In each of these fields I have added a criteria of :

Month(Date())-1

and

Year(Date())

This allows me to get the data for last month (say February), but also ensures it is only the data for this year. (as the DB holds data for the past 4 or 5 years)

The problem is though, when the year rolls over to January, this will not work.

Can anyone shed any light on an alternative please ?

Thanks,

J.

 

[neileg]

Look at the DateAdd() function. It allows you to subtract months from your date maintaining the correct year information.

 

[jonnymenthol]

Thanks very much - I'll look into that.

 

[raskew]

Hi -

The following query, which you can adapt to your needs, prompts for the mm/yyyy (e.g. 02/2006) and uses the DateValue() and DateAdd() functions to build a between #mm/dd/yyyy# and #mm/dd/yyyy# criteria.

When the DateValue() function is fed just the month and year, it assumes the day is the 1st. Using the DateAdd() function to add one month to this date returns the first day of the following month, and subtracting one day from this returns the last day of the target month, regardless of the number of days in the month (28, 29, 30, 31).

SELECT
    tblPayments.AccountID
  , tblPayments.dteLastPaid
  , tblPayments.AmtPaid
FROM
   tblPayments
WHERE
   (((tblPayments.dteLastPaid) Between DateValue([enter mm/yyyy]) 
AND
   DateAdd("m",1,DateValue([enter mm/yyyy])-1)));

HTH - Bob

 

[ciskid]

Hi raskew

I just thought I would let you know that I used your code above and it was very helpfull.

Cheers

Jimmy

 

[raskew]

Jimmy -

Glad it was helpful.

Best wishes - Bob