Date Ranges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Troy W - 03 May 2005 18:03 GMT

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.

Reply to this Message

Allen Browne - 03 May 2005 22:36 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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
[quoted text clipped - 3 lines]
>
> Troy W.

Reply to this Message

Troy W - 03 May 2005 23:54 GMT

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.

> Function SetFilterDates(strLabelName As String)
> Dim dtStart As Date
[quoted text clipped - 43 lines]
>>
>> Troy W.

Reply to this Message

Allen Browne - 04 May 2005 03:27 GMT

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

You can figure the others from there.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 51 lines]
>>>
>>> Troy W.

Reply to this Message