(Date()-1)Workday???

[firestorm998]

I'm currently using the (Date()-1) function in various fields on a report which is generated each day usining prior day data.

By process of elimination I've come up with the below control source formula to try and account for Weekdays as when the report is run on a Monday it needs to be dated (Date()-3) ie the prior Friday not (Date()-1) being Sunday.

=IIf(Weekday(Date())=2,(Date()-3),(Date()-1))

Is there an easier way of acheiving this? I use the (Date()-1) in various file save macros & auto email subjects etc so I'm not to sure about including the IIF statement in those areas?

Thanks

 

[HiTechCoach]

I would create a function in a code module. Something like:

Public Function GetPreviousWorkDate(pDate As Date) As Date

    If Weekday(pDate) = 2 Then
       GetPreviousWorkDate = pDate - 3
    Else
       GetPreviousWorkDate = pDate - 1
    End If

End Function

Anywhere you need the previous date , substitute (Date()-1) with:

GetPreviousWorkDate(Date())

Note: This could also be used with any date, not just the current clock's date by passing a different date.

 

[gemma-the-husky]

i would be inclined to use vbconstants, rather than numbered values - much clearer

Public Function GetPreviousWorkDate(pDate As Date) As Date
    select case Weekday(pDate)
    case vbmonday:        GetPreviousWorkDate = pdate-3
    case vbsunday:        GetPreviousWorkDate = pdate-2
    case else:               GetPreviousWorkDate = pdate-1
    end select
End Function

 

[firestorm998]

Thanks for the responses folks.

I'm not too familiar with Functions but iIve created one and when I enter the control source of a label (i set up just to test) on a form I can use the expression builder to locate the function & select it.

My control source then reads:

=GetPreviousWorkDate([«pDate»])

However when I view the form its returning a ~#Name? error?

I've tried this with each of your codes.

 

[HiTechCoach]

Try:

=GetPreviousWorkDate(Date())