Calculating Dates 3

[tbaguio]

Hi,

Is there a way to get the following information from the database using the current date?

Current Date + 2 weeks on a working day Friday (in Canada)

Essentially, I want to find out what date is the date two weeks from now on the Friday. I've reviewed most of the other date formulae posts and nothing seems to fit my situation. Does anyone have any suggestions?

 

[scking]

The simple answer is, yes. If you are able to think of it and the design supports it then you should be able to solve it.

My first suggestion is that there is no date formulae, only built-in date functions that provide the needed capability to solve a problem. When confronted with a problem, obviously the first avenue to turn to is a simple function but if that does not exist then it will be necessary to think out the problem.

Add two weeks to todays date
If it is a Friday then return the date
Else if it is not a Friday then
Search Forward for up to six days
If seach date is a Friday then
Return the seach date
otherwise look at the next date
End If
End Search
End If

It then becomes a simple process of locating the functions that can support your logic. I made the assumption that if it is not a Friday to search forward. Your problem statement wasn't explicit about what to do. The issue is if it is not the correct way you want it done then modify you can modify the logic and make the changes. This worked on simple testing.

Public Function dt()

Dim dtAdded As Date
Dim strDay As String
Dim intCtr As Integer

dtAdded = DateAdd("ww", 2, Date)

For intCtr = 1 To 6
strDay = Format(dtAdded, "dddd&quot
Select Case strDay
Case "Friday"
dt = dtAdded
Exit Function
Case Else
dtAdded = DateAdd("d", 1, dtAdded)
End Select
Next intCtr

End Function

 

[MichaelRed]

Not really much different in concept, but it avoids the repetitious dateadd, which I have found to be QUITE time consuming.

Public Function basSecdFri(Optional MyDate As Variant) As Date

    Dim tstDate As Date             'Trial date to return
    Dim DaysToAdd As Integer        'How many days from Now till Friday

    If (IsMissing(MyDate)) Then     'Check for Arg.  Allow Flixability
        tstDate = Now()             'Missing Arg, Assume ToDay
     Else
        tstDate = MyDate            'Use Arg as Date
    End If

    DaysToAdd = vbFriday - WeekDay(tstDate)     'test date till Fri
    If (WeekDay(tstDate) = vbSaturday) Then     'If Saturday
        DaysToAdd = 6                           'Just Make it 6
    End If

    basSecdFri = Format(DateAdd("d", 14 + DaysToAdd, tstDate), "Short DAte")

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Another variant on the theme. To Generalize the process, I added another Optional argument for the Number of weeks. Also 'renamed' the function so the name was more representative of the calculation (I did retain the default the original two weeks)

Public Function basSelFri(Optional MyDate As Variant, Optional NWks As Variant) As Date

    Dim tstDate As Date             'Trial date to return
    Dim DaysToAdd As Integer        'How many days from Now till Friday
    Dim MyWks As Integer

    If (IsMissing(NWks)) Then
        MyWks = 2
     Else
        MyWks = NWks
    End If

    If (IsMissing(MyDate)) Then     'Check for Arg.  Allow Flixability
        tstDate = Now()             'Missing Arg, Assume ToDay
     Else
        tstDate = MyDate            'Use Arg as Date
    End If

    DaysToAdd = vbFriday - WeekDay(tstDate)     'test date till Fri
    If (WeekDay(tstDate) = vbSaturday) Then     'If Saturday
        DaysToAdd = 6                           'Just Make it 6
    End If

    basSelFri = Format(DateAdd("d", (7 * MyWks) + DaysToAdd, tstDate), "Short Date")

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[albywalt]

You don't have to do this with code. The following can be used in a query or a calculated control. It essentially adds 3 weeks to the current date and then subtracts the number of days since Friday. Of course, you can write a function that does this; it's not much different than MichaelRed's function above.

Date()+21-Weekday(Date(),7)

Jeff

 

[tbaguio]

Hi,

Thanks for all your responses, I tried them all but ended up using a variation on albywalt's suggestion since it was the simplest. I'm impressed at the speed of which you all responded. Once again I'm a devout believer in the Access forum here at Tek Tips!

Theresa "Sleep is the best meditation." - Dalai Lama