Hi

I have a database that pulls data from another system Monday to Saturday & from this I produce month to date reports. The problem I have is when we start a new month. I can't figure out an easy way to get the first of the month & the end of the month when the current date is the 1st of the month.

I use this in a query: >=DateSerial(Year(Date()),Month(Date()),1) And <=[Forms]![frmLastDate]![Last_Date]

Which works fine but on the 1st the frmLastDate is the last day of the previous month so it's looking for e.g 01/08/2009 to 31/07/2009.

Any help would be greatly appreciated

Thanks in advance

>=DateSerial(Year(Date()),Month(Date()),1) And <=iif([Forms]![frmLastDate]![Last_Date]<DateSerial(Year(Date()),Month(Date()),1),Date(),[Forms]![frmLastDate]![Last_Date])

If effect it is looking at the end date and testing if it is less than the start date, if it is, then use today's date, otherwise use the default date.

David

Thanks for the response David.

I'm not sure if the below will do what I need (forgive my ignorance if it will)

The date of the [Forms]![frmLastDate]![Last_Date] is always the previous day so today when I ran the report it would have been ran from 01/08/2009 to 06/08/2009. But come September 1st when I run the report the [Forms]![frmLastDate]![Last_Date] will be 31/08/2009 but the code I'm using is trying to run the query from 01/09/2009 to 31/08/2009 which it can't do.

Thanks Again

>=DateSerial(Year(Date()),Month(Date()),1) And <DateSerial(Year(Date()),Month(Date())+1,1)

Will for anydate... i.e. today find the first of this month and the first of next month...

Thanks namliam

But I don't think that will work. I think the above is looking for >=01/08/2009 And <01/09/2009 but on the 01/09/2009 when I run my report using the above it will be looking for >=01/9/2009 And <01/10/2009. I think I need some sort of If statement to say if Today is firstdayofmonth then >=DateSerial(Year(Date()),Month(Date()),1)-1 And <DateSerial(Year(Date()),Month(Date())+1,1) but not sure how that would look.

Sorry if I'm not making it clear what I'm trying to achieve

Here are 2 functions

Function FirstOfThisMonth(Optional dtmDate As Date = 0) As Date
Dim Msg As String
On Local Error GoTo FirstOfThisMonth_Err

If dtmDate = 0 Then
dtmDate = Date
End If

FirstOfThisMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

FirstOfThisMonth_End:
Exit Function

FirstOfThisMonth_Err:
Msg = "Error #: " & Format$(Err.Number) & vbCrLf
Msg = Msg & Err.Description
MsgBox Msg, vbInformation, "FirstOfThisMonth"
Resume FirstOfThisMonth_End

End Function

Function LastDayOfMonth(AnyDate As Date) As Date
LastDayOfMonth = DateAdd("d", -1, FirstOfNextMonth(AnyDate))
End Function


Function FirstOfNextMonth(Optional dtmDate As Date = 0) As Date
Dim Msg As String
On Local Error GoTo FirstOfNextMonth_Err

If dtmDate = 0 Then
dtmDate = Date
End If

FirstOfNextMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 1)

FirstOfNextMonth_End:
Exit Function

FirstOfNextMonth_Err:
Msg = "Error #: " & Format$(Err.Number) & vbCrLf
Msg = Msg & Err.Description
MsgBox Msg, vbInformation, "FirstOfNextMonth"
Resume FirstOfNextMonth_End

End Function

Yes on Sept 1 it will be >=01/09/2009 And <01/10/2009

What do you want/need it to be on Sept 1?

on Sept 1 or the 1st of any month I need it to look at the whole of the previous month so >=01/08/2009 And <=31/08/2009. But then on the 2nd of Sept I then need it to be <=01/09/2009 And <=31/09/2009 which is why i thought of some sort of if statement.

Thanks

Here is a link to a demo. Does this help?

Link (http://www.access-programmers.co.uk/forums/showthread.php?t=169101)


David

So you basicaly always want to act like it is yesterday...

>=DateSerial(Year(Date()-1),Month(Date()-1),1) And <DateSerial(Year(Date()-1),Month(Date()-1)+1,1)

Thanks David a very useful demo with food for thought.

Thanks namliam yes that's correct. I've tested the above by changing the date on the system clock to 1/09/2009 & then running the query & it has brought back last months data so it seems to do what i need. Thank You.