Hi all,

I'm trying to pick up records between two ranges. Previously I have managed to select pick out the previous months entries using the following modules.

Function FirstDayOfLastMonth2() As Date
FirstDayOfLastMonth2 = DateSerial(Year(Date), Month(Date) - 1, 1)
End Function

Function LastDayOfLastMonth2() As Date
LastDayOfLastMonth2 = DateSerial(Year(Date), Month(Date), 0)
End Function

I then used a query to select the records between these two ranges.

My question is slightly different now. I'm trying to select the following range;

the entries between today's date and the last seven days of the previous month. I have tried playing around with =today()

Function lastweekofpreviousmonth() As Date
lastweekofpreviousmonth =
DateSerial(Year(Date), Month(Date) - 1, 0-7)
End Function

Function today() As Date
today = now()
End Function

Does not work (tried different permutations)

Grateful for comments/advice

Regards

Using DatePart you can get today's day-of-the-month. Let's call it X.

X - X + 1 = the first of this month. The last seven days of last month started on X - X + 1 - 7 = DATE() - X - 6.

So your date is Between Date() - X - 6 And Date()

Or something like that.

Hi all,

I'm trying to pick up records between two ranges. Previously I have managed to select pick out the previous months entries using the following modules.

Function FirstDayOfLastMonth2() As Date
FirstDayOfLastMonth2 = DateSerial(Year(Date), Month(Date) - 1, 1)
End Function

Function LastDayOfLastMonth2() As Date
LastDayOfLastMonth2 = DateSerial(Year(Date), Month(Date), 0)
End Function

I then used a query to select the records between these two ranges.

My question is slightly different now. I'm trying to select the following range;

the entries between today's date and the last seven days of the previous month. I have tried playing around with =today()

Function lastweekofpreviousmonth() As Date
lastweekofpreviousmonth =
DateSerial(Year(Date), Month(Date) - 1, 0-7)
End Function

Function today() As Date
today = now()
End Function

Does not work (tried different permutations)

Grateful for comments/advice

Regards

I tried Date and Access complained of an undefined column name. I changed it to the function Date(), and Access was fine with it.

DateSerial(Year(Date()),Month(Date()),-6) Gives you the beginning of the range
DateSerial(Year(Date()),Month(Date()),0) Gives you the end of the range

hi all,

my mistake......

what i should be extracting is the following.

During the first seven days of current month i should be able to extract the all the previous months entries and the current month. On the 8th day the routine should reset and only capture the current month's entries. And so on ..

Regards