Default Friday Date

F

face

I want to set a default date field as follows.

Get todays date - Date()
Convert it to Day of the Week
Add number of days that would change Todays date to Fridays date.

So if TodaysDate = Thursday add 1 to get Fridays date. If Wed, add 2 and so
on.

Any ideas?
 
6

'69 Camaro

Try:

Public Function calcFriDate(dt As Date) As Date

On Error GoTo ErrHandler

Dim nDay As Long
Dim nAddDays As Long

nDay = Weekday(dt, vbSunday)

nAddDays = vbFriday - nDay
calcFriDate = DateAdd("d", nAddDays, dt)

Exit Function

ErrHandler:

MsgBox "Error in calcFriDate( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
calcFriDate = dt

End Function

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
D

Douglas J Steele

What do you want to happen on the weekend: the previous Friday, or the next
Friday?

Ignoring that consideration for now, for weekdays try:

DateAdd("d", vbFriday - Weekday(MyDateField), MyDateField)
 
P

peregenem

face said:
I want to set a default date field as follows.

Get todays date - Date()
Convert it to Day of the Week
Add number of days that would change Todays date to Fridays date.
Click to expand...

At the engine level, I don't think this is possible e.g.

CREATE TABLE Test (
data_col DATETIME
DEFAULT CDATE(DATE() + 1)
NOT NULL)

generates a syntax error :(
 
S

Steve Schapel

Face,

Set the Default Value property of the field in Table design, or the
control on your form, to...
Date()-Weekday(Date(),7)+7
 
P

peregenem

Steve said:
Set the Default Value property of the field in Table design, or the
control on your form, to...
Date()-Weekday(Date(),7)+7
Click to expand...

I stand corrected: you *can* set this default at the engine (table)
level, just not via SQL DDL.
 
Top