Calculate weekending date from any day

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table and the primay key is the weekending date field. I need a
default value on this field so that I can have the employee's enter in
information from any day and the weekending date would than return the
saturday date. Can I do this as a default Value calculations in the table?

Thanks in advance!
 
This function can be used to do what you seek; put it in a regular module:

' start of code
Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.

On Error Resume Next

DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function
' end of code


Then, the Saturday date for the week containing today's date would be
SaturdayDate = DateOfSpecificWeekDay(Date(), 7)
 
Thanks for the reply, now I am still new to access, how would I connect this
to my weekending field on a table or form? I need the weekending to show up
on a new form each week.

Thanks again in advance.
 
Here is another way to do what you want:

Place the following in the "Default Value" property of the Weekending field:

=DateAdd("d",7-Weekday(Date()),Date())

This will always place the date of the next Saturday.
 
Your date could be any one of 52 (or so) values each year. I'm not sure how
you could set a default value in this table. You could set the default value
to:
=DateAdd("d",-Weekday(Date())+7,Date())
which would default to the Saturday of the current week. I would probably
set the default in the control on your form used for data entry.
 
Everyone thank you for helping! They both worked! Now just one more
question. If I make this into a single data entry form with just that weeks
totals, when I start a new week it should be with the new weekend date and
not change the previous records to the current date right, because I set up
the primary key to be for that week's ending date right? Does the weekending
date than change to the new weekending once monday starts?
 
If you use the expression that I suggested, the new/default date will be the
Saturday of the current week. If this isn't what you want, you need to tell
us what week ending date you need.
 
Back
Top