Working Days

[Hazel]

In my queries i have two fields

1st Field is called Termination Date
2nd Field is called Due Date:
3rd Field is called Date Completed:
4th Field is called (+/-):

Termination Date = I enter a date such as 20/05/05
Due Date = Automatically fills in using the expression below 17/06/05
The Due Date has an expression builder i.e.
Termination Notice-Due Date: [Termination Date]+28

Date Completed = I enter a data such as 18/06/05
(+/-) = Automatically fills in with -1

The problem is that Termination Notice-Due Date Automatically filled in using the above expression but it counts saturdays and sundays i dont want saturdays and sundays counted in the +28

Can this be done or iam i going nuts

please see attached this might help


Hazel

 

[Mile-O]

You've asked this before and I gave you the code on this thread. If you don't understand it then please say so.

Also, the fourth field is redundant and can be removed from your table. You would use a query to calculate it at runtime.

 

[Hazel]

Hi

Sorry about that was a bit confused and trying to do too many things at once.

This works but now I have errors comming up on the due date field how can i sort this

Hazel

 

[EMP]

Originally Posted by SJ McAbney
You've asked this before and I gave you the code on this thread

.
This was the code you posted on that thread:

Public Function WorkingDays(ByVal lngDays As Long, ByVal dteStart As Date, _
    ByVal booDirection As Boolean) As Long
    
    Do While lngDays <> 0
        Select Case Weekday(dteStart)
            Case Is = 2, 3, 4, 5, 6
                lngDays = lngDays - 1
        End Select
        If booDirection Then
            dteStart = dteStart + 1
        Else
            dteStart = dteStart - 1
        End If
    Loop
    
    WorkingDays = [b]dteStart[/b]
    
End Function

Since your code ignored the fact that the final dteStart may fall on a weekend, when direction is True, dteStart may fall on a Saturday, and when direction is False, it may fall on a Sunday.

For example:-

? Format(WorkingDays(8,#5/4/2005#,True), "Long Date")
Saturday, May 14, 2005


^

 

[Hazel]

You've asked this before and I gave you the code on this thread. If you don't understand it then please say so.

Also, the fourth field is redundant and can be removed from your table. You would use a query to calculate it at runtime.

Thanks for that but i iam still getting a small problem
When i enter at date such as Monday 06/06/05 my due date that counts 5 working days gives me a result of Saturday 11/06/05 is there a way of resolving this so that sat is not included.
Also due to this table being linked to another all due dates come up with an error in the field can this be resolved or iam i going crazy.