date functions

  • Thread starter Thread starter JOE
  • Start date Start date
J

JOE

We sell services that reoccur every month. I have the
date of the first service lets say 9/1/03. This is the
first Monday of Sept. I need to recreate these services
for the rest of the year all on the first Monday of the
month

Is there a function or does anyone have any sample code to
get the first Monday or whatever day of the month I enter
for every month that follows the date I enter? It could
be the first wed, or the 3rd Friday etc.....

Example:
I enter 9/1/03
I need a function to return
10/6/03,11/3/03,12/1/03,1/5/04 ...

Thanks,
Joe
 
Here is a date function that should work ...Sun = 1, Mon=2 ...

Function FirstDay(Month, Year, DayOfWeek)
Dim i
For i = 1 To 7
If WeekDay(Month & "/" & i & "/" & Year) = DayOfWeek Then
FirstDay = i
Exit For
End If
Next
End Function

Sub Test()
'Example First Thursday in June
Debug.Print FirstDay(6, 2003, 5)
End Sub

-- Kirby
 
I'd stongly suggest changing the line

If WeekDay(Month & "/" & i & "/" & Year) = DayOfWeek Then

to

If WeekDay(DateSerial(Year, Month, Day)) = DayOfWeek Then

so that you don't have to rely on Access coercing a string into a date. If
the user's got their Short Date format set to other than mm/dd/yyyy, you'll
get unexpected results from that line.

--
Doug Steele, Microsoft Access MVP



Here is a date function that should work ...Sun = 1, Mon=2 ...

Function FirstDay(Month, Year, DayOfWeek)
Dim i
For i = 1 To 7
If WeekDay(Month & "/" & i & "/" & Year) = DayOfWeek Then
FirstDay = i
Exit For
End If
Next
End Function

Sub Test()
'Example First Thursday in June
Debug.Print FirstDay(6, 2003, 5)
End Sub

-- Kirby
 
Back
Top