Date calculations - please tell me where I am going wrong

[Guest]

Hello All

I wonder if anyone can help.

I am trying to add some code to a form to calculate the first working day of
each month for the next six months...

here's how far I have got...

For j = 1 To 6

dayd = Format(1, "dd")
monthd = Month(Now) + j

If monthd > 12 Then
monthd = Format(Month(Now) + j - 12, "mm")
yeard = Format(Year(Now) + 1, "yyyy")
Else
monthd = Format(Month(Now) + j, "mm")
yeard = Format(Year(Now), "mm")
End If

dated = dayd & "/" & monthd & "/" & yeard

If Weekday(dated) = 1 Then
dayd = Format(2, "dd")
dated = dayd & "/" & monthd & "/" & yeard
End If

If Weekday(dated) = 7 Then
dayd = Format(3, "dd")
dated = dayd & "/" & monthd & "/" & yeard
End If

If j = 1 Then
pymt1date = Format(dated, "DD/MM/YYYY")
End If

If j = 2 Then
pymt2date = Format(dated, "DD/MM/YYYY")
End If

If j = 3 Then
pymt3date = Format(dated, "DD/MM/YYYY")
End If

If j = 4 Then
pymt4date = Format(dated, "DD/MM/YYYY")
End If

If j = 5 Then
pymt5date = Format(dated, "DD/MM/YYYY")
End If

If j = 6 Then
pymt6date = Format(dated, "DD/MM/YYYY")
End If

Next j

However this doesn't seem to work!

Can anyone suggest any better way of doing this please?

Any comments would be much appreciated.

Many thanks in advance and kind regards

Nick

 

[Jeff Boyce]

Nick

Already responded to in the formscoding newsgroup.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Douglas J. Steele]

How have you declared dated (and dayd)?

Assuming the first working day will be Monday if the first day of the month
is a Saturday or Sunday and that pymt1date, pymt2date are controls on your
form, try:

Dim dated As Date
Dim j As Long

For j = 1 To 6
dated = DateSerial(Year(Date), Month(Date) + j, 1)
Select Case Weekday(DateD)
Case vbSaturday
dated = DateAdd('d", 2, dated)
Case vbSunday
dated = DateAdd('d", 1, dated)
End Select
Me.Controls("pymt" & j & "date") = Format(dated, "dd/mm/yy")
Next j

DateSerial work fine, even when Month(Date) + j is greater than 12