default to last working date in week

[gymphil]

Can anyone please advise on how to set a forms control to default to a date
(last Friday in current week) in the format yyyymmdd. New records are added
throughout the week but the default date should always be the Friday.

Thanks

 

[Erez Mor]

hi
for countries with sunday=1 and saturday=7,
to find next friday's date, set the DefaultValue property of the control to:
=DateAdd("d", 6 - Weekday(Date), Date)
this will set the date to the current week's friday - no matter which day of
the week is it (including saturday!!)

good luck
erez.

 

[gymphil]

Erez,

Thanks for the reply.
As you suggested I added =DateAdd("d", 6 - Weekday(Date), Date) as the
default but I now get #NAME? displayed in the control, any ideas?

Phil

 

[Linq Adams via AccessMonster.com]

Erez Mor's formula will work, of course, as will others. But I prefer

Date + (vbFriday - Weekday(Date))

because it's less cryptic. Looking at it, anyone can easily figure out that
to return another day, you simply replace the day constant; vbMonday for
Monday, vbTuesday for Tuesday, etc. To format it as the OP asked, you'd use

Format(Date + (vbFriday - Weekday(Date)),"yyyymmdd")

 

[Linq Adams via AccessMonster.com]

Access gets snarly as to what's allowed in Default Values. For this I'd just
use it in the Form_Current event:

Private Sub Form_Current()
If Me.NewRecord Then
Me.YourDate = Date + (vbFriday - Weekday(Date))
End If
End Sub

Just replace YourDate with the actual name of your date control on the form.

 

[gymphil]

Linq,

Thanks very much for your assistance, works a treat!

Phil