Just wanted to share a tid bit
Ever have a db which has records that include a date? Sometimes you might need to sort records such as this by the WeekEnding?
I had such a issue and with a time reporting database, and during the development of reports, it got a little difficult with the way that dates are sorted and grouped in charts and running sums. So I came up with this little function that will return the next following Sunday (Weekending) date of any date that is passed to it.
'********************************************************
Function GetWeekEnding(dDate As Date) As Date
'determines Sunday's(Week Ending)
'date from any date to enter into the table
Dim vdate As Integer 'var to hold result of weekday function
Dim sDate As Date 'Original date argument, modified to next following Sunday
sDate = dDate 'Set date to argument date
vdate = Weekday(dDate) 'Gets integer of weekday of argument
Select Case vdate 'From weekday value, determine what weekday it is and
'add appropriate days/dates to make equal with Sunday
Case Is = 1 'Is Sunday, nothing added
GetWeekEnding = DateAdd("d", 0, sDate)
Case Is = 2 'Monday, 6 days added
GetWeekEnding = DateAdd("d", 6, sDate)
Case Is = 3 'Tuesday, 5 days added
GetWeekEnding = DateAdd("d", 5, sDate)
Case Is = 4 'Wednesday, 4 days added
GetWeekEnding = DateAdd("d", 4, sDate)
Case Is = 5 'Thurday, 3 days added
GetWeekEnding = DateAdd("d", 3, sDate)
Case Is = 6 'Friday, 2 days added
GetWeekEnding = DateAdd("d", 2, sDate)
Case Is = 7 'Saturday, 1 day added
GetWeekEnding = DateAdd("d", 1, sDate)
End Select
End Function
Is used this function when a new record is added or modified, passing the date entered and recording the Weekending date in another field.
This way, it's really easy to sort by week and by day for buisness oriented reporting and calculating.
Let me know if any questions or a better way of doing something like this,
Thanks!
Ever have a db which has records that include a date? Sometimes you might need to sort records such as this by the WeekEnding?
I had such a issue and with a time reporting database, and during the development of reports, it got a little difficult with the way that dates are sorted and grouped in charts and running sums. So I came up with this little function that will return the next following Sunday (Weekending) date of any date that is passed to it.
'********************************************************
Function GetWeekEnding(dDate As Date) As Date
'determines Sunday's(Week Ending)
'date from any date to enter into the table
Dim vdate As Integer 'var to hold result of weekday function
Dim sDate As Date 'Original date argument, modified to next following Sunday
sDate = dDate 'Set date to argument date
vdate = Weekday(dDate) 'Gets integer of weekday of argument
Select Case vdate 'From weekday value, determine what weekday it is and
'add appropriate days/dates to make equal with Sunday
Case Is = 1 'Is Sunday, nothing added
GetWeekEnding = DateAdd("d", 0, sDate)
Case Is = 2 'Monday, 6 days added
GetWeekEnding = DateAdd("d", 6, sDate)
Case Is = 3 'Tuesday, 5 days added
GetWeekEnding = DateAdd("d", 5, sDate)
Case Is = 4 'Wednesday, 4 days added
GetWeekEnding = DateAdd("d", 4, sDate)
Case Is = 5 'Thurday, 3 days added
GetWeekEnding = DateAdd("d", 3, sDate)
Case Is = 6 'Friday, 2 days added
GetWeekEnding = DateAdd("d", 2, sDate)
Case Is = 7 'Saturday, 1 day added
GetWeekEnding = DateAdd("d", 1, sDate)
End Select
End Function
Is used this function when a new record is added or modified, passing the date entered and recording the Weekending date in another field.
This way, it's really easy to sort by week and by day for buisness oriented reporting and calculating.
Let me know if any questions or a better way of doing something like this,
Thanks!