Automatic Dates in Access

Topic

New Reply

Hello,
We’ve been using the attached (very stripped down) database to print timecard label every week. We just purchased a new time clock which does not indicate the current week’s date. I would like to add a “from” date and a “to” date on the label. I’m not sure how to do it. I would also like it to be automatic so the person who does it doesn’t have to “mess” with anything and get frustrated.

Since we do these weekly, I would like the “from date” to always be the upcoming Sunday and the “to date” to always be the following Saturday. Can this be done easily? I really don’t want to go the date stamp route.

Thanks!

Reply | Quote

Replies

In playing around with the report, it looks like I can put a formula in the Control Source for the seperate text boxes. I tried the =Date() and it worked beautifully. Now I just need to figure out what formula to enter in there to get the future dates I need.

Thanks

Reply | Quote

PS – attached is copy of your sample database, I modified the report to illustrate how to use the functions in report textbox – see textbox ControlSource property:

=GetFirstDayOfWeek(Date(),1)

(I had to reformat slightly so date would fit in box.) Note – as is case with queries, you cannot used named VBA constants in ControlSource expression, you’d have to use the numerical equivalent. This doesn’t apply in the case of your report because the week begins with Sunday, which is the default in VBA.

HTH

Reply | Quote

Mark,
That looks great! Thank you! Now, I’ll have to look behind the scene and see what you did and duplicate it. The original database is quite involved and hopefully I’ll figure out how to incorporate this as well.

Thanks again. Be prepared for more questions?

Reply | Quote

Hi Mark,
I was able to export your hard work into my real database without any problems! Also exported the report and everything went fine. Thank you so much!

Reply | Quote

Louise,

Glad worked OK. One final note I neglected to mention earlier, if for some reason did not want to use functions, as alternative could use the following expressions as the Control Source in the textboxes on report to calculate the next Sunday and following Saturday. For “FROM” date:

=Date()-Weekday(Date())+8

For “TO” date:

=Date()-Weekday(Date())+14

This should provide same results. I like to use functions in some cases because more flexible, and if needed in more than one place in database you don’t have to re-invent the wheel & figure out the formula again. But if not familiar with VBA code, using expressions like the examples above are a perfectly valid & simpler alternative.

HTH

Reply | Quote

This is great Mark! I will look into this alternative as well!

Thanks again,

Reply | Quote

Below are examples of some user-defined functions you could use to calculate the beginning of week and end of week dates, relative to a specified date:

Private Const DAYS_IN_WEEK = 7

Public Function GetFirstDayOfWeek(ByRef DateRef As Date, _
Optional ByRef WeekOffset As Long = 0, _
Optional ByRef FirstDayOfWeek As VbDayOfWeek = vbSunday) As Date
On Error GoTo Err_Handler
Dim strMsg As String

' Assumes Sun as default 1st day of week if FirstDayOfWeek not specified
GetFirstDayOfWeek = DateRef - Weekday(DateRef, FirstDayOfWeek) + (DAYS_IN_WEEK * WeekOffset) + 1

Exit_Proc:
Exit Function
Err_Handler:
Select Case Err.Number
Case 0
Resume Next
Case Else
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "GetFirstDayOfWeek Function - Unexpected Error"
Resume Exit_Proc
End Select
End Function

Public Function GetLastDayOfWeek(ByRef DateRef As Date, _
Optional ByRef WeekOffset As Long = 0, _
Optional ByRef FirstDayOfWeek As VbDayOfWeek = vbSunday) As Date
On Error GoTo Err_Handler
Dim strMsg As String

' Assumes Sun as default 1st day of week if FirstDayOfWeek not specified
GetLastDayOfWeek = DateRef - Weekday(DateRef, FirstDayOfWeek) + DAYS_IN_WEEK * (WeekOffset + 1)

Exit_Proc:
Exit Function
Err_Handler:
Select Case Err.Number
Case 0
Resume Next
Case Else
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "GetLastDayOfWeek Function - Unexpected Error"
Resume Exit_Proc
End Select
End Function

Examples of use (current week):

? GetFirstDayOfWeek(Date())
5/21/2006
? GetLastDayOfWeek(Date())
5/27/2006

‘To get next Sunday, relative to today:
? GetFirstDayOfWeek(Date(),1)
5/28/2006
‘ To get next Saturday, relative to today:
? GetLastDayOfWeek(Date(),1)
6/3/2006

Optional parameters are used to make the functions more flexible, in case the “week” needs to be defined other than the VBA default, where Sunday is first day of week and Saturday the last day of week, and to allow “offsets”. Example using optional parameters:

? GetFirstDayOfWeek(#1/1/2006#,-1,vbMonday)
12/19/2005
? GetLastDayOfWeek(#1/1/2006#,-1,vbMonday)
12/25/2005

The sample code can be copied into a standard code module and then used in your application where needed. Note: if using function in query expression, replace the “VbDayOfWeek ” constant with its numerical value, ie, vbSunday = 1, vbMonday = 2, etc.

HTH

Reply | Quote

Holy smokes Mark! This must be something that goes into the Visual Basic somewhere? Will have to decipher all you gave me to see if I can find something that will work for me.

Thanks

Reply | Quote