Stopping a date building string. 1

[pdldavis]

Hi, I have some code here I am working on where I am building a string of dates for a month excluding Saturdays and Sundays. This much working.

I need to replace the For - Next statement with something like While it is this month, build the string. If a date falls into the next month, stop building the string and I do not know exactly how to do that.

That is what the dim Nextmonth is for - eventually stopping building of the string.

I could use some help.

The Code:

Private Sub cmdGetDates_Click()

Dim TheFirst As Date

Dim ThisYear, ThisMonth, NextMonth, FirstDay, NextDay As Integer

Dim StrSql As String


ThisMonth = Month(Now())
NextMonth = ThisMonth + 1 'Use this for stopping the string building
ThisYear = Year(Now())


'Get the first day of the month

FirstDay = DateSerial(ThisYear, ThisMonth, 1)

'Find the Weekday of the first of the Month that is not Saturday or Sunday
'Want to Ignore Weekends. Saturday = 7, Sunday = 1

'MsgBox WeekDay(FirstDay)
Select Case WeekDay(FirstDay) 'If Saturday or Sunday, set to Monday

Case Is = 7 'If Saturday, Add 2 to The First

FirstDay = FirstDay + 2

Case Is = 1 'If Sunday, Add 1 to The First

FirstDay = TheFirst + 1

Case Else: 'Any other date, use that date

FirstDay = FirstDay


End Select

'Try Four Weeks and see if it works.

'Start Building Sql String

StrSql = FirstDay & ", "
'''''''''''''''''''''''''''''''''''''''''''''''
For x = FirstDay To FirstDay + 31

'I need to replace this with something that says while the month is the current month,
'build the sql string. Stop building when FirstDay falls into another month.

'''''''''''''''''''''''''''''''''''''''''''''''


FirstDay = FirstDay + 1

If WeekDay(FirstDay) = 1 Then 'Saturday
FirstDay = FirstDay + 3

ElseIf WeekDay(FirstDay) = 7 Then 'Sunday
FirstDay = FirstDay + 2

ElseIf WeekDay(FirstDay) = 2 Then 'Monday
FirstDay = FirstDay

ElseIf WeekDay(FirstDay) = 3 Then 'Tuesday
FirstDay = FirstDay

ElseIf WeekDay(FirstDay) = 4 Then 'Wednesday
FirstDay = FirstDay

ElseIf WeekDay(FirstDay) = 5 Then 'Thursday
FirstDay = FirstDay

ElseIf WeekDay(FirstDay) = 6 Then 'Friday
FirstDay = FirstDay

End If

StrSql = StrSql & FirstDay & ", "


Debug.Print FirstDay

Next x


Debug.Print StrSql


End Sub


Thanks, Dan

 

[PHV]

Something like this ?

Private Sub cmdGetDates_Click()
Dim ThisMonth As Integer, FirstDay As Date, StrSql As String
ThisMonth = Month(Now())
'Get the first day of the month
FirstDay = DateSerial(Year(Now()), ThisMonth, 1)
Do
  'Want to Ignore Weekends. Saturday = 7, Sunday = 1
  Select Case Weekday(FirstDay)  'If Saturday or Sunday, set to Monday
  Case 7 'If Saturday, Add 2 to The First
    FirstDay = FirstDay + 2
  Case 1 'If Sunday, Add 1 to The First
    FirstDay = FirstDay + 1
  'Any other date, use that date
  End Select
  If Month(FirstDay) <> ThisMonth Then Exit Do
  'Build Sql String
  StrSql = IIf(Len(StrSql), StrSql & ",#", "#") & FirstDay & "#"
  FirstDay = FirstDay + 1
Loop
Debug.Print StrSql
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pdldavis]

Yes, that will do it. Thanks for the help and the more compact example.
Dan