Weekend function 1

[Moxy1]

I need to pull info based on a date field, where if today is Monday, pull anything from the prior Friday, Sat or Sun, otherwise pull just yesterday. Is there a way to do this?

Thanks,
Moxy

 

[Norris68]

Public Function PrevWorkingDay(InDate As Date) As Date
Dim CurrentDay As Integer
CurrentDay = Weekday(InDate, vbSaturday)
If CurrentDay <= 3 Then
PrevWorkingDay = DateAdd(&quot;d&quot;, -1 * CurrentDay, InDate)
Else
PrevWorkingDay = DateAdd(&quot;d&quot;, -1, InDate)
End If
End Function

 

[substitute]

Great idea to start the week on Saturday. Saves complexity. Worth a *.

Norris68's VBA code is so neat it also works as a query criteria expression if you want to avoid VBA:

>=IIf(Weekday(date(),7)<=3,date()-Weekday(date(),7),date()-1)

Incidentally, a lot of people put unnecessary complexity in date and time arithmetic. Dates are stored as numbers of days so you can just add/subtract the days. DateAdd is good for things like months where the number of days varies.

 

[christak]

This was very helpful! I've been trying to figure out how to do this forever! Now I have another question...How do I get this date to show on my report header?
Thanks!

 

[substitute]

Put an unbound text box in the report header and set the control source to

=IIf(Weekday(date(),7)<=3,date()-Weekday(date(),7),date()-1)

or, if you created Noris68's VBA function

=PrevWorkingDay(date())

You could concatenate the date with other text such as

=&quot;Report for period to &quot; & iif(....

and you can format it in different ways using the Format() function.