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)

--

Ken Snell
<MS ACCESS MVP>

"mgkaam" <(E-Mail Removed)> wrote in message
news:819285A4-86FB-4574-82B1-(E-Mail Removed)...
>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!
>

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.


"Ken Snell (MVP)" wrote:

> 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)
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "mgkaam" <(E-Mail Removed)> wrote in message
> news:819285A4-86FB-4574-82B1-(E-Mail Removed)...
> >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!
> >
>
>
>

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.
--
HTH

Mr B


"mgkaam" wrote:

> 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!
>

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.
--
Duane Hookom
Microsoft Access MVP


"mgkaam" wrote:

> 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!
>

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" wrote:

> 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.
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "mgkaam" wrote:
>
> > 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!
> >

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.

--
Duane Hookom
Microsoft Access MVP


"mgkaam" wrote:

> 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" wrote:
>
> > 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.
> > --
> > Duane Hookom
> > Microsoft Access MVP
> >
> >
> > "mgkaam" wrote:
> >
> > > 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!
> > >