You could write a little function to do it, e.g.
Public Function GetIssueDate(Optional varDate As Variant) As Date
Dim dtmdate As Date
Dim intDaysBack As Integer
' use current date if no date passed into function
If IsMissing(varDate) Then varDate = VBA.Date()
' determine number of days to be subtracted
Select Case Weekday(varDate, vbTuesday)
Case Is < 6
intDaysBack = -1
Case 6
intDaysBack = -2
Case 7
intDaysBack = -3
End Select
' subtract relevant number of days
GetIssueDate = DateAdd("d", intDaysBack, varDate)
End Function
Just paste this into a standard module in the database and save the module
under a different name to the function, e.g. mdlDateStuff. Then in a query
to get the business day before the current date call it like so:
Issue Date: GetIssueDate()
or if you want the business day before another date either pass a date itself
into the function, e.g. for yesterday (Monday)
Issue Date: GetIssueDate(#2010-01-25#)
or if you want the date before a date in another field in the table pass a
refernce to the field in:
Issue Date: GetIssueDate([Other Date Field Name])
This does not take account of public or concessionary holidays, however. If
you want to exclude them, e.g. if you call it on a Tuesday and the Monday
before is a public holiday, you'd want the previous Friday, then you'd need
to store the holidays in a table and look them up so thy could be discounted.
The following more generalised function does this:
Public Function WorkDaysAdd(intDays As Integer, Optional dtmDateFrom As
Variant) As Date
Dim dtmdate As Date
Dim n As Integer
Dim intIncr As Integer
' use crrent date if no date passed into function
If IsMissing(dtmDateFrom) Then dtmDateFrom = VBA.Date
' value by which date will be incremented
' each day is 1 or -1 depending on sign of
' intdays argument
intIncr = intDays / Abs(intDays)
' initialize date from which to count
dtmdate = dtmDateFrom
' add days one by one
For n = 1 To Abs(intDays)
dtmdate = DateAdd("d", intIncr, dtmdate)
' skip weekends and holidays
Do While Weekday(dtmdate, vbMonday) > 5 Or _
Not IsNull(DLookup("HolDate", "Holidays", _
"HolDate = #" & Format(dtmdate, "yyyy-mm-dd") & "#"))
dtmdate = DateAdd("d", intIncr, dtmdate)
Loop
Next n
WorkDaysAdd = dtmdate
End Function
In this case each holiday date is stored in a column HolDate in a table
Holidays.
As this function adds days you need to pass the number of days to be
subtracted as a negative number so to get the working day before the current
date;
Issue Date: WorkDaysAdd(-1)
or before a specific date:
Issue Date: WorkDaysAdd(-1, #2010-01-25#)
or before the date of another column in the table:
Issue Date: WorkDaysAdd(-1, [Other Date Field Name])
BTW the format in which a date is returned is by default the system short
date format in use, but you can format it however you wish by setting the
Format property of the computed column in the query as desired.
There is of course no reason why you cannot put both of these functions in
the mdlDateStuff module and call whichever is most suited to a specific
requirement in a query, form or report.
Ken Sheridan
Stafford, England
