Custom Date Function Nightmare!!

[Louise]

I'm doing a custom function to get the last workday in the month. This will be used in an update query on (obviously) the date field.

The function first gets the last day of the month.
I then test that day with 2 additional functions to see if that day is a Saturday or Sunday.
If Saturday, Date = Date - 1 day
If Sunday, Date = Date - 2 days.

When I run this through my update query :

Format(dhLastWorkdayInMonth(Date()),"dd-mmm-yy")

It gives me August 30 instead of 31. August 31 is a Thursday so this should not happen. Can anyone think why this is hapenning??

This is the code:


Function dhLastWorkdayInMonth(dtmDate As Date) As Date
' Return the last working day in the month specified.
Dim dtmTemp As Date

Select Case dtmTemp
Case IsSaturday(dtmTemp)
dhLastWorkdayInMonth = dtmTemp - 1
Case IsSunday(dtmTemp)
dhLastWorkdayInMonth = dtmTemp - 2
Case Else
dhLastWorkdayInMonth = dtmTemp
End Select

End Function

Private Function IsSaturday(dtmTemp As Date) As Boolean
' Determines if the last day of the month falls on a Saturday.
Dim dtmDate As Date

dtmDate = Date
'get the last day of month
dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)

'see if last day of month is a Saturday
Select Case WeekDay(dtmTemp)
Case vbSaturday
IsSaturday = True
Case Else
IsSaturday = False
End Select

End Function


Private Function IsSunday(dtmTemp As Date) As Boolean
' Determines if the last day of the month falls on a Sunday.
Dim dtmDate As Date

dtmDate = Date
'get last day of month
dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)

'see if last day of month is a Sunday
Select Case WeekDay(dtmTemp)
Case vbSunday
IsSunday = True
Case Else
IsSunday = False
End Select

End Function

 

[Jack Cowley]

Try this code:
Function LastDate(NewDate As Date) As Date
NewDate = DateSerial(Year(NewDate), Month(NewDate) + 1, 0)
If WeekDay(NewDate) = 1 Then
NewDate = NewDate - 2
End If
If WeekDay(NewDate) = 7 Then
NewDate = NewDate - 1
End If
LastDate = NewDate
End Function

If you send the code 9/3/00 it will return 9/29/00. If you send it 8/1/00 it will return 8/31/00.

HTH,
Jack

 

[Louise]

Thanks Jack!
That works terrific now.