Movenext not moving - Help Please

[Mo]

Hi,

I've wrote this function to loop through a table of Holiday dates in
order to calculate a business day three days in the future that was not
a weekend nor a holiday.

When I tested it as a function in a query, it worked fine.

I then took the code to a sub on the AfterUpdateEvent and added the dim
db, dim rst, do...loop, etc.

When I set breakpoints, to check that the code is moving through the
recordset, the holidate never changes. It's always starts and stays at
1/2/2006, which is not the first record in the Holiday table, but is
the 12th record. In the immediate window if I type rstHoliday.movenext
and then ?holidate, I still get 1/2/2006. (BTW Holidate is the primary
key of the Holiday table).

Would someone please take the time to look at this - is my loop in the
wrong place?

Thanks so much.

Here's the code:

Private Sub AsOfTradeDate_AfterUpdate()

'This sub is used to calculate the settlemet date using the default
'3 day settlement from as of trade date and takes into account
'weekends and holidays. The holidays are from the Holiday Table which
must be kept up by
'the user each year.

Dim db As Database
Dim AsOfDate As Date
Dim HoliDate As Date
Dim WeekDayNumber
Dim rstHoliday As DAO.Recordset
Dim rstIndex As index

Set db = CurrentDb()
Set rstHoliday = db.OpenRecordset("Holidays")

AsOfDate = Format(Me.AsOfTradeDate, "Short Date")
WeekDayNumber = Weekday(Me.AsOfTradeDate)
HoliDate = rstHoliday!HoliDate
HoliDate = Format(HoliDate, "short Date")
rstHoliday.MoveLast
rstHoliday.MoveFirst

Do While rstHoliday.EOF = False
If HoliDate > AsOfDate And HoliDate < DateAdd("d", 6, AsOfDate)
Then
Select Case WeekDayNumber
'WeekDayNumber is 2 Monday
Case 2
If DateAdd("d", 1, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 4, AsOfDate)
ElseIf DateAdd("d", 2, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 4, AsOfDate)
ElseIf DateAdd("d", 3, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 4, AsOfDate)
Else
Me.SettlementDate = DateAdd("d", 3, AsOfDate)
End If
'WeekDayNumber is 3 Tuesday
Case 3
If DateAdd("d", 1, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
ElseIf DateAdd("d", 2, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
ElseIf DateAdd("d", 3, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
Else
Me.SettlementDate = DateAdd("d", 3, AsOfDate)
End If

'WeekDayNumber is 4 Wednesday
Case 4
If DateAdd("d", 1, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
ElseIf DateAdd("d", 2, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
ElseIf DateAdd("d", 5, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
Else
Me.SettlementDate = DateAdd("d", 5, AsOfDate)
End If

'WeekdayNumber is 5 Thursday
Case 5
If DateAdd("d", 1, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
ElseIf DateAdd("d", 4, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
ElseIf DateAdd("d", 5, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
Else
Me.SettlementDate = DateAdd("d", 5, AsOfDate)
End If

'WeekdayNumber is 6 Friday
Case 6
If DateAdd("d", 3, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
ElseIf DateAdd("d", 4, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
ElseIf DateAdd("d", 5, AsOfDate) = HoliDate Then
Me.SettlementDate = DateAdd("d", 6, AsOfDate)
Else
Me.SettlementDate = DateAdd("d", 5, AsOfDate)
End If

End Select
End If
rstHoliday.MoveNext
Loop

Set rstHoliday = Nothing

End Sub

 

[Douglas J. Steele]

Move your statement

HoliDate = rstHoliday!HoliDate

inside the loop: you're never resetting it as you move from row to row!

I'd also suggest you rename it, to avoid possible confusion with the field
name. I always prefix my date variables with dtm, so that I'd have

Dim dtmHoliDate As Date

and

dtmHoliDate = rstHoliday!HoliDate

FWIW, the line of code HoliDate = Format(HoliDate, "short Date") does
nothing. Date fields hold dates, which are stored as 8 byte floating numbers
(where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day). Format is irrelevant to them. If what you're trying to
do is eliminate the time portion from the value, use

dtmHoliDate = DateValue(rstHoliday!HoliDate)

 

[Mo]

Thank you so very much Doug. It didn't even dawn on me that the
setting of the holidate was in the wrong place (i.e., outside the
loop).

Also thanks too for the info on dates and naming standards. You're
right on - I was having issues with the time portion. I'll use your
standard, very nice.

Have a great evening.

Mo.

 

[Klatuu]

Naming conventions! Love, couldn't live without them. Here is a link to a
great source for naming conventions:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

 

[RD]

How timely! We just formed an applications standards committee! Thanks for
this.

RD

 

[Tim Ferguson]

How timely! We just formed an applications standards committee!

This is way out of date even by Microsoft standards... shame the apps
groups ever got hold of the Hungarian stuff. Try this for a heads up:

http://www.joelonsoftware.com/articles/Wrong.html

All the best


Tim F

 

[RD]

This is way out of date even by Microsoft standards... shame the apps
groups ever got hold of the Hungarian stuff. Try this for a heads up:

http://www.joelonsoftware.com/articles/Wrong.html

All the best


Tim F

Thanks. That took me into some interesting areas.