Is Day In String of days (1 Viewer)

Dreamweaver · May 19, 2019

Working Days Is A Sting of days monday - friday in this case in the format of "2,3,4,5,6," as is in My employee example
The code below loops testing the string until it returns 0 I.E. day not found

It moves the "EndDate" to the last available working day in that week I.E Friday

The code below works but had to play with it to get it working and I feel It is very messy could anybody help me clean it up

Code:

Private Sub StartDate_AfterUpdate()
Dim I As Integer, O As Integer
Dim F As Integer
Me.Dirty = False
F = 1
O = Weekday(StartDate) + 1
I = Weekday(StartDate)

    Do While F <> 0
        I = I + 1
        F = InStr(Me![WorkingDays], I)
        'Debug.Print F, I, Me![WorkingDays], O
    Loop
    I = I - O
    Me![EndDate] = DateAdd("d", I, Me![StartDate])
End Sub

June7 · May 19, 2019

Exactly what are you looking for - last weekday in that string or last working day? What if string is "2,3,5,6,7" or "2,4,6"?

Can pull the last element with Right(Me![WorkingDays], 1)

theDBguy · May 19, 2019

Hi Mick. I'm not sure I follow your logic either. You assign the weekday value to I but then adds 1 to it right before you check for F. Aren't you skipping the value for StartDate, or is this by design?

Gasman · May 19, 2019

I'd probably use the day of the week and a function.
This selects the same Friday if date is a Friday. Adjust to suit.?

Code:

Function GetFriday(pDate As Date)
Dim iDays As Integer
Select Case Weekday(pDate)
    Case 1
        iDays = 5
    Case 7
        iDays = 6
    Case Else
        iDays = 6 - Weekday(pDate)
End Select
GetFriday = DateAdd("d", iDays, pDate)

End Function

Dreamweaver · May 19, 2019

Thanks I'm adding a picture below you will see there are 2 dates both must be on a working day as in my preferences (Same as the working days in my employee example) I.E . "2,3,4,5,6," or the list could be only "4,5,6" The start might be monday or any day between as long as it's in the lists above.
what then happens is the end day is moved x days but not beyond say friday as that is the last day in the list I.E. 6

full code as is below

Code:

Dim I As Integer, O As Integer
Dim F As Integer

On Error GoTo HandleErr

Me.Dirty = False

If InStr(Me![WorkingDays], Weekday(Me![StartDate])) = 0 Then MsgBox "The Start Date you have selected is not a working day as set in preferences", vbInformation + vbOKOnly, "Working Days"
F = 1
O = Weekday(StartDate) + 1
I = Weekday(StartDate)

    Do While F <> 0
        I = I + 1
        F = InStr(Me![WorkingDays], I)
        'Debug.Print F, I, Me![WorkingDays], O
    Loop
    I = I - O
    Me![EndDate] = DateAdd("d", I, Me![StartDate])
    
HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case 2501 'Cancel = True
            Exit Sub
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select

As you can see I'm not making them keep to the working days as they may be a very rare occations they might book weekend so I left that open this is like a wizard that will work with my diary

Gasman · May 19, 2019

Don't forget you can change the start day of week, so Monday becomes 1.?
This would have selected the next friday if on a friday as well.
So you do not want a day > 5 if using vbMonday ?

Code:

Function NextFriday(pDate As Date)
Dim iDays As Integer

If Weekday(pDate, vbMonday) > 5 Then
    iDays = 12 - Weekday(pDate, vbMonday)
ElseIf Weekday(pDate, vbMonday) = 5 Then
    iDays = 7
Else
    iDays = 5 - Weekday(pDate, vbMonday)
End If
NextFriday = DateAdd("d", iDays, pDate)

End Function

Dreamweaver · May 19, 2019

Hi Gasman my system does not rely on what the system is set too the company may start there week on tueday as long as all the days are in the list I should end up with the last day this may be sunday depending on the comany in the case above saturday = 7 and sunday 1

the system will only allow them to book x days within the list of there working days which is set in preferences and could be 1,2,3,4,5, or 2,3,4,5,6

I will have to look at the system week as not sure now but that could be down to the code in the preferences that creates the list thanks

P.S. It will only allow them to book up to one week to each employee but if they start a job on wednesday and there week ends on friday they will only be able to book 3 days to that job but they can run the tool more than once so they can schedule there employees each week.

This is the related project some of the images need updating and there it still over 250 hrs work to go
https://databasedreams.createaforum.com/works-manager/user-manual-43/

Gasman · May 19, 2019

So you just need to compare to the last day in the string?
This might do it?

Code:

Function EndOfWeek(pDate As Date)
Dim dtDate As Date
Dim strdays As String

strdays = "2,3,4,5,6"
dtDate = pDate
Do Until CStr(Weekday(dtDate)) = Right(strdays, 1)
    dtDate = DateAdd("d", 1, dtDate)
    Debug.Print dtDate & " " & WeekdayName(Weekday(dtDate), , vbSunday)
Loop
EndOfWeek = dtDate
End Function

sonic8 · May 19, 2019

MickJav said:
The code below works but had to play with it to get it working and I feel It is very messy could anybody help me clean it up

I agree on the code being messy. My reasons for this assessment might be different than yours.

- The variables are poorly named.
- It does too many things (saving the form, validating the start date, and calculating the end date)
- It is dependent on the form controls

Here is my suggestion for the end date calculation:

Code:

Public Function GetEndDate(ByVal workingDaysList As String, ByVal startDate As Date) As Date

    Dim endDate     As Date
    
    Dim workingDays As Variant
    workingDays = Split(workingDaysList, ",")
    
    Dim lastWorkDayOfWeek As Integer
    lastWorkDayOfWeek = workingDays(UBound(workingDays))

    endDate = DateAdd("d", lastWorkDayOfWeek - Weekday(startDate), startDate)
    
    GetEndDate = endDate

End Function

Solo712 · May 19, 2019

Gasman said:
Don't forget you can change the start day of week, so Monday becomes 1.?
This would have selected the next friday if on a friday as well.
So you do not want a day > 5 if using vbMonday ?

Code:

Function NextFriday(pDate As Date) Dim iDays As Integer If Weekday(pDate, vbMonday) > 5 Then iDays = 12 - Weekday(pDate, vbMonday) ElseIf Weekday(pDate, vbMonday) = 5 Then iDays = 7 Else iDays = 5 - Weekday(pDate, vbMonday) End If NextFriday = DateAdd("d", iDays, pDate) End Function

A simpler way to do it would be :

Code:

NextFriday = pDate + 8 - Weekday(pDate, vbFriday)

Best,
Jiri

Solo712 · May 19, 2019

MickJav said:
Thanks I'm adding a picture below you will see there are 2 dates both must be on a working day as in my preferences (Same as the working days in my employee example) I.E . "2,3,4,5,6," or the list could be only "4,5,6" The start might be monday or any day between as long as it's in the lists above.
what then happens is the end day is moved x days but not beyond say friday as that is the last day in the list I.E. 6

full code as is below

Code:

Dim I As Integer, O As Integer Dim F As Integer On Error GoTo HandleErr Me.Dirty = False If InStr(Me![WorkingDays], Weekday(Me![StartDate])) = 0 Then MsgBox "The Start Date you have selected is not a working day as set in preferences", vbInformation + vbOKOnly, "Working Days" F = 1 O = Weekday(StartDate) + 1 I = Weekday(StartDate) Do While F <> 0 I = I + 1 F = InStr(Me![WorkingDays], I) 'Debug.Print F, I, Me![WorkingDays], O Loop I = I - O Me![EndDate] = DateAdd("d", I, Me![StartDate]) HandleExit: Exit Sub HandleErr: Select Case Err.Number Case 2501 'Cancel = True Exit Sub Case Else MsgBox Err.Number & vbCrLf & Err.Description Resume HandleExit Resume End Select

As you can see I'm not making them keep to the working days as they may be a very rare occations they might book weekend so I left that open this is like a wizard that will work with my diary

MickJav, have you actually try to run this code? Did you get any error messages? There is at least one glaring error that I see (I did not look further!). The statement

Code:

F = InStr(Me![WorkingDays], I)

should be flagged by the compiler because "I" is defined as integer and both arguments of the InStr function must be strings.

Best,
Jiri

Dreamweaver · May 19, 2019

No Error the instr can handle an integer as by its very nature it may be reqired to eval a number as in this case it returns a number that being the location in a sting of either the letter or number in this case as there are ony 12 charators an integer works fine

Dreamweaver · May 19, 2019

sonic8 said:
I agree on the code being messy. My reasons for this assessment might be different than yours.

- The variables are poorly named.
- It does too many things (saving the form, validating the start date, and calculating the end date)
- It is dependent on the form controls

Here is my suggestion for the end date calculation:

Code:

Public Function GetEndDate(ByVal workingDaysList As String, ByVal startDate As Date) As Date Dim endDate As Date Dim workingDays As Variant workingDays = Split(workingDaysList, ",") Dim lastWorkDayOfWeek As Integer lastWorkDayOfWeek = workingDays(UBound(workingDays)) endDate = DateAdd("d", lastWorkDayOfWeek - Weekday(startDate), startDate) GetEndDate = endDate End Function

Nice I didn't think of using an array I'll give it a go tomorrow thanks

Solo712 · May 20, 2019

MickJav said:
No Error the instr can handle an integer as by its very nature it may be reqired to eval a number as in this case it returns a number that being the location in a sting of either the letter or number in this case as there are ony 12 charators an integer works fine

So, you are saying you have run the code and received no VBA error, correct?

Best,
Jiri

Dreamweaver · May 20, 2019

Hi Sonic8 It gives me a type mismatch on below line:

lastWorkDayOfWeek = WorkingDays(UBound(WorkingDays))

I haven't done a lot with arrays so would take time I haven't got to fix it but I do like the concept for it working that way.

At the moment I'll keep with the old way but will see if I can find time to get your way working thanks all

Solo712 As I said it works just fine my only issue with my code was I was sure there was a better way if doing it than what I had dreamed up thanks for the help.

sonic8 · May 20, 2019

MickJav said:
Hi Sonic8 It gives me a type mismatch on below line:

lastWorkDayOfWeek = WorkingDays(UBound(WorkingDays))

I can't reproduce this. What input were you using to cause that error?

Maybe there is a conflict, because you've got a control named WorkingDays on your form. - But I think that shouldn't be a problem.

Dreamweaver · May 20, 2019

sonic8 said:
I can't reproduce this. What input were you using to cause that error?

Maybe there is a conflict, because you've got a control named WorkingDays on your form. - But I think that shouldn't be a problem.

At work at the moment but will change the working days as could be the problem, I'm calling the function with the (me![workingdays],me![startdate]) the error is internal to the unbound() from what I could see when debugging it thanks mick

sonic8 · May 20, 2019

MickJav said:
I'm calling the function with the (me![workingdays],me![startdate]) the error is internal to the unbound() from what I could see when debugging it

What is the value of me![workingdays] in that case?

I was able to reproduce the error if your list of days is ending with a comma instead of a day number. e.g.: 1,2,3,
Then error does happen because the expression UBound(workingDays) returns an empty string, which causes the type mismatch when it is used to indicate the element in the array.

Gasman · May 20, 2019

Solo712 said:
A simpler way to do it would be :

Code:

NextFriday = pDate + 8 - Weekday(pDate, vbFriday)

Best,
Jiri

Cannot get much tidier than that.

Dreamweaver · May 20, 2019

sonic8 said:
What is the value of me![workingdays] in that case?

I was able to reproduce the error if your list of days is ending with a comma instead of a day number. e.g.: 1,2,3,
Then error does happen because the expression UBound(workingDays) returns an empty string, which causes the type mismatch when it is used to indicate the element in the array.

Will check that but am wondering what will happen if the user works 7 days I.E. 1,2,3,4,5,6,7 my code F would never = 0 so would need something like DO WHILE F<>0 or I=7

Will check the string when home and correct the preferences code so it removes the ending , if there would your work with a 7 day working week and thank for all the help

Is Day In String of days (1 Viewer)

Well-known member

AWF VIP

I’m here to help

Enthusiastic Amateur

Well-known member

Attachments

Enthusiastic Amateur

Well-known member

Enthusiastic Amateur

AWF VIP

Registered User.

Registered User.

Well-known member

Well-known member

Registered User.

Well-known member

AWF VIP

Well-known member

AWF VIP

Enthusiastic Amateur

Well-known member

Similar threads

Users who are viewing this thread