RE: Return a weekday on a query

This should help:

Public Function dtm_gfctDate_U_Need( _
ByVal dtmDate As Date) As Date

Dim ysnGotDate As Boolean
Dim rsRecordset As Recordset

dtm_gfctDate_U_Need = DateAdd("d", -2, dtmDate)

ysnGotDate = False
While Not ysnGotDate
Select Case Weekday(dtm_gfctDate_U_Need)
Case 1
dtm_gfctDate_U_Need = DateAdd("d", -2, dtm_gfctDate_U_Need)
Case 7
dtm_gfctDate_U_Need = DateAdd("d", -1, dtm_gfctDate_U_Need)
Case Else
Set rsRecordset = CodeDb.OpenRecordset( _
"SELECT dtmHoliday FROM tblHolidays WHERE dtmHoliday =
#" & Format(dtm_gfctDate_U_Need, "mm/dd/yy") & "#", dbOpenSnapshot)
If rsRecordset.EOF Then
ysnGotDate = True
Else
dtm_gfctDate_U_Need = DateAdd("d", -1,
dtm_gfctDate_U_Need)
End If
End Select
Wend

Set rsRecordset = Nothing

End Function

Copy it into a module in an Access dB and test it using the Immediate Window
using a call like this:

?dtm_gfctDate_U_Need(CDate("03/01/07")) ' where the date is in dd/mm/yy
format

You'll need to create a table named tblHolidays (with one field named
dtmHoliday) in which you enter a record with each date which is a public
holiday.

Cheers.




"RobertM" wrote:

Hello:

Here's my situation. This database is being used to track cash dividends.
Most dividends have what is called a "Ex" date. In some rare cases there is
no Ex date given to us. What we need to do is subtract two days from the
record (Rec) date. If the subtracting two from the record date gives us a
weekend date we need to go with Friday's date. Here's the somewhat useless
IIf that I've written that dosen't see day of the week:

Ex: IIf([TblBkData].[Ex] Is Null,[TblBkData].[Rec]-2,[TblBkData].[Ex])

Any help is greatly appreciated!

Robert
.