Results 1 to 4 of 4

Thread: finding friday

  1. #1
    Join Date
    Aug 2004
    Posts
    178

    Unanswered: finding friday

    i have a textbox which the user puts in the date and the user uses this to print all there reports. which all work fine. i now have to create a report which i must find the friday before the textbox but if the textbox = a fri then that is the date that will be needed. can anyone help

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    checkout the function
    weekday(someDate)

    DELETED the example code - it was wrong, and gotta go eat now.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this should leave someDate as Friday or switch it to the previous Friday:
    someDate = dateadd("d", 1-weekday(someDate, vbFriday), someDate)

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    here is ssome code I wrote

    Code:
    Function MyWeekEndDate(dat) As Date
    Dim Daydiff
    If IsNull(dat) Then Exit Function
    '********************************
    ' This Will return a Weekending Date
    ' Must set the Daydiff =
    ' 0 = Sat , 6 = Sun
    ' 5 = Mon , 4 = Tue
    ' 3 = Wed , 2 = Thu
    ' 1 = Fri
    ' Setting Daydiff to to a Above day Should Return that Date in
    ' the week in question
    '********************************
    Daydiff = 1
    dat = DateSerial(Year(dat), Month(dat), Day(dat))
    If dat Mod 7 > 0 Then
    MyWeekEndDate = (dat - dat Mod 7 + 7) - Daydiff
    Else
    MyWeekEndDate = dat - Daydiff
    End If
    End Function
    It will return a weekendinf date base in the daydiff

    Paste code into a module

    the in a Query

    weekend:MyWeekEndDate([invoice date])

    it will add a new feild to the query and

    you should beable to groupyou data by weekend:MyWeekEndDate()
    and get weekly totals
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •