Date Ranges

T

Troy W

I am trying to come up with some code to select a report date. I would like
the calculate the dates for the current week, or current year, last year,
last month, ect. Any ideas would be really great.

Thanks,

Troy W.
 
A

Allen Browne

Function SetFilterDates(strLabelName As String)
Dim dtStart As Date
Dim iYear As Integer

'Start of this quarter.
dtStart = DateSerial(Year(Date), 3 * (DatePart("q", Date) - 1) + 1, 1)
'Start of this financial year.
iYear = Year(Date) - (Month(Date) > 6) - 1

Select Case strLabelName
Case "lblThisQtr"
Me.txtStartDate = dtStart
'Me.txtEndDate = DateAdd("q", 1, dtStart) - 1

Case "lblLastQtr"
'Me.txtEndDate = dtStart - 1
Me.txtStartDate = DateAdd("q", -1, dtStart)

Case "lblPriorQtr"
Me.txtStartDate = DateAdd("q", -2, dtStart)
'Me.txtEndDate = DateAdd("q", -1, dtStart) - 1

Case "lblThisYear"
Me.txtStartDate = DateSerial(iYear, 7, 1)
'Me.txtEndDate = DateSerial(iYear + 1, 6, 30)

Case "lblLastYear"
Me.txtStartDate = DateSerial(iYear - 1, 7, 1)
'Me.txtEndDate = DateSerial(iYear, 6, 30)

Case "lblPriorYear"
Me.txtStartDate = DateSerial(iYear - 2, 7, 1)
'Me.txtEndDate = DateSerial(iYear - 1, 6, 30)

Case Else
MsgBox strLabelName & " not handled.", vbInformation,
"SetFilterDates()"
End Select
End Function
 
T

Troy W

Ok so I have gotten the dates to work for this month, this year, last month,
ect, but I am still not able to figure out how to figure out the dates for
the current week, or for say last week.

Thanks,

Troy W.
 
A

Allen Browne

For the start of this week:
Date - Weekday(Date) + 1

You can figure the others from there.
 
Top