Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Default Friday Date

Discussion in 'Information Technology' started by face, Oct 28, 2005.

  1. face

    face
    Expand Collapse
    Guest

    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?
     
  2. Loading...


  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    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.


    "face" wrote:

    > 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?
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "face" <face@discussions.microsoft.com> wrote in message
    news:F682AB35-5606-4136-A5C5-AE4E0A135A33@microsoft.com...
    > 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?
     
  5. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    face wrote:
    > 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.


    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 :(
     
  6. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Face,

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

    --
    Steve Schapel, Microsoft Access MVP


    face wrote:
    > 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?
     
  7. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

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


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

Share This Page