Calculate weekending date from any day

[mgkaam]

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!

 

[Ken Snell \(MVP\)]

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)

 

[mgkaam]

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.

 

[Mr B]

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.

 

[Duane Hookom]

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.

 

[mgkaam]

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?

 

[Duane Hookom]

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.