Date Ranges

  • Thread starter Thread starter Troy W
  • Start date Start date
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.
 
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
 
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.
 
For the start of this week:
Date - Weekday(Date) + 1

You can figure the others from there.
 
Back
Top