Date Problem (working days)

[aingram]

Hi guys, im not sure if this is the right board to post this problem on, but what the hay ill give it a go.

I have a database for issing work orders to our workers i have a issue date that is calculated by the computer date() function. I also have a combo box with different options for a target period (ie. 1 day, 3 working days, 28 days, 6 months etc)

It works perfect just now apart from working days, i have seen many different ways of working this out but my limited knowlage cannot work it into my database.

Select Case Target_Combo
    Case "Emergency"
        Me.target_completion_date = Me.ISSUE_DATE
                
    Case "3 Working Days"
        Me.target_completion_date = DateAdd("d", 21, Me.ISSUE_DATE)
                
    Case "28 Days"
        Me.target_completion_date = DateAdd("d", 28, Me.ISSUE_DATE)
        
    Case "2 Months"
        Me.target_completion_date = DateAdd("m", 2, Me.ISSUE_DATE)
        
    Case "4 Months"
        Me.target_completion_date = DateAdd("m", 4, Me.ISSUE_DATE)

    Case "6 Months"
        Me.target_completion_date = DateAdd("m", 6, Me.ISSUE_DATE)

        End Select

Any guidence on doing this would be fantastic

 

[allan57]

Select Case Target_Combo

Case "Emergency"
Me.target_completion_date = Me.ISSUE_DATE

Case "3 Working Days"
Me.target_completion_date = PlusWorkingDays(ISSUE_DATE, 3)

Case "28 Days"
Me.target_completion_date = PlusWorkingDays(ISSUE_DATE, 28)

'etc

End Select

Public Function PlusWorkingDays(dteStartFrom As Date, intNummberOfDays As Integer) As Date

On Error Resume Next

PlusWorkingDays = dteStartFrom

Do While intNummberOfDays > 0

PlusWorkingDays = DateAdd("d", 1, PlusWorkingDays)

If WeekDay(PlusWorkingDays, vbMonday) <= 5 Then

intNummberOfDays = intNummberOfDays - 1

End If

Loop

End Function

 

[aingram]

Thanks for the reply allan

ive tryed your code and function ( i made a new module called plusworkingdays )
but when i chose the from the combo i get the attached message

 

[allan57]

aingram

You can't have a module name the same as procedure name, change your module name to something like modDate_Utilities or whatever you like as long as it's different.

Allan

 

[aingram]

Cheers allan!!!!!

i had to change this though

Select Case Target_Combo

Case "Emergency"
Me.target_completion_date = Me.ISSUE_DATE

Case "3 Working Days"
Me.target_completion_date = PlusWorkingDays(me.ISSUE_DATE, 3)

Case "28 Days"
Me.target_completion_date = PlusWorkingDays(me.ISSUE_DATE, 28)

'etc

End Select


THANKS