Return a weekday on a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Hi -

The following allows the user to specify the number of business days to
backup:

Function BackBusDays2(pStart As Date, _
pnum As Integer) As Date
'*******************************************
'purpose: Output date if pNum business
' days (Mon - Fri) are subtracted
' from pStart.
'coded by: raskew
'Inputs: from debug (immediate) window)
' ? BackBusDays2(#2/22/06#, 3)
'Output: 2/17/06
'*******************************************

Dim dteHold As Date
Dim i As Integer
Dim n As Integer

dteHold = pStart - 1
n = 0
Do While n < pnum
i = WeekDay(dteHold)
n = n + IIf(i = 1 Or i = 7, 0, 1)
dteHold = dteHold - 1
Loop

BackBusDays2 = dteHold + 1
End Function

HTH - Bob
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.
Hello:
Click to expand...
[quoted text clipped - 10 lines]
Robert
Click to expand...
Click to expand...
 
Back
Top