Pop message last businessday of month

S

Shawn

I am creating “msgbox†that tell user billing due on end of month
(workingday) when they click on cmd button
I had it work last day of the month but I having a problem to get last
business day of the month. I am using Access 2003. I will appreciate any
help on this.
Here is the code

Dim MyDate As String
Dim Eomday, dtLast

MyDate = Date

Eomday = DateSerial(Year(Date), Month(Date) + 1, 0)

If Weekday(Eomday, vbSaturday) Or Weekday(Eomday, vbSunday) Then
dtLast = Eomday - (Weekday(Eomday, vbSunday) - 6)
End If

If MyDate > Eomday Then
MsgBox "MONTHLY BILLING NOT DUE Till " & Chr(13) & [dtLast],
vbInformation, "Monthly Billing"
Exit Sub
End If
 
D

Douglas J. Steele

Sorry, I missed the fact that your use of the Weekday function is incorrect.
It needs to be

If Weekday(Eomday) = vbSaturday Or Weekday(Eomday) = vbSunday Then
dtLast = Eomday - (Weekday(Eomday, vbSunday) - 6)
Else
dtLast = Eomday
End If


Just to test it, I wrote the following procedure:

Sub EOM()
Dim dtmEOM As Date
Dim dtmLast As Date
Dim lngMonth As Long

For lngMonth = 1 To 12
dtmEOM = DateSerial(2009, lngMonth + 1, 0)
Select Case Weekday(dtmEOM)
Case vbSaturday, vbSunday
dtmLast = dtmEOM - (WeekDay(dtmEOM, vbSunday) - 6)
Case Else
dtmLast = dtmEOM
End Select
Debug.Print Format(dtmEOM, "mm\-dd dddd") & " ==> " & Format(dtmLast,
"mm\-dd dddd")
Next lngMonth

End Sub

Here's the output it gave me for the last business day of each month this
year:

01-31 Sat ==> 01-30 Fri
02-28 Sat ==> 02-27 Fri
03-31 Tue ==> 03-31 Tue
04-30 Thu ==> 04-30 Thu
05-31 Sun ==> 06-05 Fri
06-30 Tue ==> 06-30 Tue
07-31 Fri ==> 07-31 Fri
08-31 Mon ==> 08-31 Mon
09-30 Wed ==> 09-30 Wed
10-31 Sat ==> 10-30 Fri
11-30 Mon ==> 11-30 Mon
12-31 Thu ==> 12-31 Thu


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Shawn said:
The problem I am having is if any giving month end on Saturday or Sunday I
need the date for last business day of that giving month for example
January
ended on Saturday witch was 31, last business day was 30th , February last
business day would be 27th .
If I use this expression" dtLast = Eomday - Weekday(Eomday, vbSaturday)"
witch gives me last Friday of the month, for example March 09 ending on
Tuesday 31, I am getting last business day as 27th instead and April 24,
2009
instead of 30th.

Thank you

Douglas J. Steele said:
You don't explain what problem you're having, but dtLast = Eomday -
(Weekday(Eomday, vbSunday) - 6) doesn't seem correct.

For instance, tomorrow (Feb 7) is Saturday and Feb 8 is Sunday (yes, I
know
they're not the end of the month...)

?Weekday(#2009-02-07#, vbSunday )
7
?Weekday(#2009-02-08#, vbSunday )
1

That means for Saturdays, you'll be subtracting 1 (which is correct), but
for Sundays, you'll be subtracting -5 (which means adding 5!)

On the other hand

?Weekday(#2009-02-07#, vbSaturday )
1
?Weekday(#2009-02-08#, vbSaturday )
2

so I think all you need is

dtLast = Eomday - Weekday(Eomday, vbSaturday)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Shawn said:
I am creating "msgbox" that tell user billing due on end of month
(workingday) when they click on cmd button
I had it work last day of the month but I having a problem to get last
business day of the month. I am using Access 2003. I will appreciate
any
help on this.
Here is the code

Dim MyDate As String
Dim Eomday, dtLast

MyDate = Date

Eomday = DateSerial(Year(Date), Month(Date) + 1, 0)

If Weekday(Eomday, vbSaturday) Or Weekday(Eomday, vbSunday) Then
dtLast = Eomday - (Weekday(Eomday, vbSunday) - 6)
End If

If MyDate > Eomday Then
MsgBox "MONTHLY BILLING NOT DUE Till " & Chr(13) & [dtLast],
vbInformation, "Monthly Billing"
Exit Sub
End If
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Oops. That should have been:

It needs to be

If Weekday(Eomday) = vbSaturday Or Weekday(Eomday) = vbSunday Then
dtLast = Eomday - Weekday(Eomday, vbSaturday)
Else
dtLast = Eomday
End If

The test procedure is

Sub EOM()
Dim dtmEOM As Date
Dim dtmLast As Date
Dim lngMonth As Long

For lngMonth = 1 To 12
dtmEOM = DateSerial(2009, lngMonth + 1, 0)
Select Case WeekDay(dtmEOM)
Case vbSunday, vbSaturday
dtmLast = dtmEOM - WeekDay(dtmEOM, vbSaturday)
Case Else
dtmLast = dtmEOM
End Select
Debug.Print Format(dtmEOM, "mm\-dd ddd") & " ==> " & Format(dtmLast,
"mm\-dd ddd")
Next lngMonth

End Sub

The result of running the test procedure is

01-31 Sat ==> 01-30 Fri
02-28 Sat ==> 02-27 Fri
03-31 Tue ==> 03-31 Tue
04-30 Thu ==> 04-30 Thu
05-31 Sun ==> 05-29 Fri
06-30 Tue ==> 06-30 Tue
07-31 Fri ==> 07-31 Fri
08-31 Mon ==> 08-31 Mon
09-30 Wed ==> 09-30 Wed
10-31 Sat ==> 10-30 Fri
11-30 Mon ==> 11-30 Mon
12-31 Thu ==> 12-31 Thu


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas J. Steele said:
Sorry, I missed the fact that your use of the Weekday function is
incorrect. It needs to be

If Weekday(Eomday) = vbSaturday Or Weekday(Eomday) = vbSunday Then
dtLast = Eomday - (Weekday(Eomday, vbSunday) - 6)
Else
dtLast = Eomday
End If


Just to test it, I wrote the following procedure:

Sub EOM()
Dim dtmEOM As Date
Dim dtmLast As Date
Dim lngMonth As Long

For lngMonth = 1 To 12
dtmEOM = DateSerial(2009, lngMonth + 1, 0)
Select Case Weekday(dtmEOM)
Case vbSaturday, vbSunday
dtmLast = dtmEOM - (WeekDay(dtmEOM, vbSunday) - 6)
Case Else
dtmLast = dtmEOM
End Select
Debug.Print Format(dtmEOM, "mm\-dd dddd") & " ==> " & Format(dtmLast,
"mm\-dd dddd")
Next lngMonth

End Sub

Here's the output it gave me for the last business day of each month this
year:

01-31 Sat ==> 01-30 Fri
02-28 Sat ==> 02-27 Fri
03-31 Tue ==> 03-31 Tue
04-30 Thu ==> 04-30 Thu
05-31 Sun ==> 06-05 Fri
06-30 Tue ==> 06-30 Tue
07-31 Fri ==> 07-31 Fri
08-31 Mon ==> 08-31 Mon
09-30 Wed ==> 09-30 Wed
10-31 Sat ==> 10-30 Fri
11-30 Mon ==> 11-30 Mon
12-31 Thu ==> 12-31 Thu


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Shawn said:
The problem I am having is if any giving month end on Saturday or Sunday
I
need the date for last business day of that giving month for example
January
ended on Saturday witch was 31, last business day was 30th , February
last
business day would be 27th .
If I use this expression" dtLast = Eomday - Weekday(Eomday, vbSaturday)"
witch gives me last Friday of the month, for example March 09 ending on
Tuesday 31, I am getting last business day as 27th instead and April 24,
2009
instead of 30th.

Thank you

Douglas J. Steele said:
You don't explain what problem you're having, but dtLast = Eomday -
(Weekday(Eomday, vbSunday) - 6) doesn't seem correct.

For instance, tomorrow (Feb 7) is Saturday and Feb 8 is Sunday (yes, I
know
they're not the end of the month...)

?Weekday(#2009-02-07#, vbSunday )
7
?Weekday(#2009-02-08#, vbSunday )
1

That means for Saturdays, you'll be subtracting 1 (which is correct),
but
for Sundays, you'll be subtracting -5 (which means adding 5!)

On the other hand

?Weekday(#2009-02-07#, vbSaturday )
1
?Weekday(#2009-02-08#, vbSaturday )
2

so I think all you need is

dtLast = Eomday - Weekday(Eomday, vbSaturday)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am creating "msgbox" that tell user billing due on end of month
(workingday) when they click on cmd button
I had it work last day of the month but I having a problem to get last
business day of the month. I am using Access 2003. I will appreciate
any
help on this.
Here is the code

Dim MyDate As String
Dim Eomday, dtLast

MyDate = Date

Eomday = DateSerial(Year(Date), Month(Date) + 1, 0)

If Weekday(Eomday, vbSaturday) Or Weekday(Eomday, vbSunday) Then
dtLast = Eomday - (Weekday(Eomday, vbSunday) - 6)
End If

If MyDate > Eomday Then
MsgBox "MONTHLY BILLING NOT DUE Till " & Chr(13) & [dtLast],
vbInformation, "Monthly Billing"
Exit Sub
End If
Click to expand...
Click to expand...
Click to expand...
 
S

Shawn

Thank you, Douglas it worked. also I would like to thank you for all those
postings out there you have for helping other people which help me so much in
correcting my DB. I truly appreciate all your advice and knowledge on these
subjects. Well deserved.

Thank you.

Douglas J. Steele said:
Oops. That should have been:

It needs to be

If Weekday(Eomday) = vbSaturday Or Weekday(Eomday) = vbSunday Then
dtLast = Eomday - Weekday(Eomday, vbSaturday)
Else
dtLast = Eomday
End If

The test procedure is

Sub EOM()
Dim dtmEOM As Date
Dim dtmLast As Date
Dim lngMonth As Long

For lngMonth = 1 To 12
dtmEOM = DateSerial(2009, lngMonth + 1, 0)
Select Case WeekDay(dtmEOM)
Case vbSunday, vbSaturday
dtmLast = dtmEOM - WeekDay(dtmEOM, vbSaturday)
Case Else
dtmLast = dtmEOM
End Select
Debug.Print Format(dtmEOM, "mm\-dd ddd") & " ==> " & Format(dtmLast,
"mm\-dd ddd")
Next lngMonth

End Sub

The result of running the test procedure is

01-31 Sat ==> 01-30 Fri
02-28 Sat ==> 02-27 Fri
03-31 Tue ==> 03-31 Tue
04-30 Thu ==> 04-30 Thu
05-31 Sun ==> 05-29 Fri
06-30 Tue ==> 06-30 Tue
07-31 Fri ==> 07-31 Fri
08-31 Mon ==> 08-31 Mon
09-30 Wed ==> 09-30 Wed
10-31 Sat ==> 10-30 Fri
11-30 Mon ==> 11-30 Mon
12-31 Thu ==> 12-31 Thu


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas J. Steele said:
Sorry, I missed the fact that your use of the Weekday function is
incorrect. It needs to be

If Weekday(Eomday) = vbSaturday Or Weekday(Eomday) = vbSunday Then
dtLast = Eomday - (Weekday(Eomday, vbSunday) - 6)
Else
dtLast = Eomday
End If


Just to test it, I wrote the following procedure:

Sub EOM()
Dim dtmEOM As Date
Dim dtmLast As Date
Dim lngMonth As Long

For lngMonth = 1 To 12
dtmEOM = DateSerial(2009, lngMonth + 1, 0)
Select Case Weekday(dtmEOM)
Case vbSaturday, vbSunday
dtmLast = dtmEOM - (WeekDay(dtmEOM, vbSunday) - 6)
Case Else
dtmLast = dtmEOM
End Select
Debug.Print Format(dtmEOM, "mm\-dd dddd") & " ==> " & Format(dtmLast,
"mm\-dd dddd")
Next lngMonth

End Sub

Here's the output it gave me for the last business day of each month this
year:

01-31 Sat ==> 01-30 Fri
02-28 Sat ==> 02-27 Fri
03-31 Tue ==> 03-31 Tue
04-30 Thu ==> 04-30 Thu
05-31 Sun ==> 06-05 Fri
06-30 Tue ==> 06-30 Tue
07-31 Fri ==> 07-31 Fri
08-31 Mon ==> 08-31 Mon
09-30 Wed ==> 09-30 Wed
10-31 Sat ==> 10-30 Fri
11-30 Mon ==> 11-30 Mon
12-31 Thu ==> 12-31 Thu


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Shawn said:
The problem I am having is if any giving month end on Saturday or Sunday
I
need the date for last business day of that giving month for example
January
ended on Saturday witch was 31, last business day was 30th , February
last
business day would be 27th .
If I use this expression" dtLast = Eomday - Weekday(Eomday, vbSaturday)"
witch gives me last Friday of the month, for example March 09 ending on
Tuesday 31, I am getting last business day as 27th instead and April 24,
2009
instead of 30th.

Thank you

:

You don't explain what problem you're having, but dtLast = Eomday -
(Weekday(Eomday, vbSunday) - 6) doesn't seem correct.

For instance, tomorrow (Feb 7) is Saturday and Feb 8 is Sunday (yes, I
know
they're not the end of the month...)

?Weekday(#2009-02-07#, vbSunday )
7
?Weekday(#2009-02-08#, vbSunday )
1

That means for Saturdays, you'll be subtracting 1 (which is correct),
but
for Sundays, you'll be subtracting -5 (which means adding 5!)

On the other hand

?Weekday(#2009-02-07#, vbSaturday )
1
?Weekday(#2009-02-08#, vbSaturday )
2

so I think all you need is

dtLast = Eomday - Weekday(Eomday, vbSaturday)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am creating "msgbox" that tell user billing due on end of month
(workingday) when they click on cmd button
I had it work last day of the month but I having a problem to get last
business day of the month. I am using Access 2003. I will appreciate
any
help on this.
Here is the code

Dim MyDate As String
Dim Eomday, dtLast

MyDate = Date

Eomday = DateSerial(Year(Date), Month(Date) + 1, 0)

If Weekday(Eomday, vbSaturday) Or Weekday(Eomday, vbSunday) Then
dtLast = Eomday - (Weekday(Eomday, vbSunday) - 6)
End If

If MyDate > Eomday Then
MsgBox "MONTHLY BILLING NOT DUE Till " & Chr(13) & [dtLast],
vbInformation, "Monthly Billing"
Exit Sub
End If
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA to go to specific sheet & cell in the current workbook not working. 2
Month problem 2
Excel Message box with days of month & date. 1
Update field w/ string based on service due dates 4
Code Optimization 0
Finding the first DAY of the week of a given week. 3
Runtime Error 1004 when runing Sub() from btnClick 2
Include/Exclude Holiday - Repost to Programming Section 1

Top