Previous month

[Rockape]

Hi all,

Hope someone can help....

I have a list of entries which includes the date. I would like to have a query which would extract the previous month's entries in relation to Date()...

in other words I have a query which extracts the current month using >=Format(Date(),"\1/mm/yy")..

I would like a similar query which extracts the previous month's entries.

Any clues?

 

[DCrake]

Simple Software Solutions

First of all you need to calculate the first & last days of the previous month.

Function FirstDayOfLastMonth() As Date
    Dim d As Date
    d = CDate("01/" & Month(Date) & "/" & Year(Date))
    FirstDayOfLastMonth = DateAdd("m", -1, d)
End Function

Function LastDayOfLastMonth() As Date
    LastDayOfLastMonth = DateAdd("d", -1, CDate("01/" & Month(Date) & "/" & Year(Date)))
End Function

Place these two functions into a module

Then in your query under the date field you want to filter enter the following code:

Between FirstDayOfLastMonth() And LastDayOfLastMonth()

This will filter out all records for the previous month.

CodeMaster:

 

[namliam]

This seems less of a mess....

Function FirstDayOfLastMonth() As Date
    FirstDayOfLastMonth = dateserial(year(date), month(date())-1,1)
End Function

as well as

Function LastDayOfLastMonth() As Date
    LastDayOfLastMonth = dateserial(year(date), month(date()),0)
End Function

It doesnt much matter, but just wanted to throw this out there, as these are one liners that can also be used very easily in a query without problems.

 

[Rockape]

Thanks worked a treat...

 

[Rockape]

Thanks this worked a treat too....

 

[Rockape]

Hi again,


Is Dateserial a date function? can't find it in the Access Help?

Kind Regards

 

[namliam]

Yes date function, just type in Dateserial in any module and hit F1.

The basic idea for Dateserial tho...
Dateserial ( Year, Month, Day )
And it will return a date...


Funny trick with dateserial is i.e. Dateserial(2008,1,0) will return 31-dec-2007
while i.e. 2008,0,0 will return 30-Nov-2007
And 2008,-1,-1 returns 30-Oct-2007

It is a really flexible function