Working Day - Idiots Guide?

[Maynard]

Hi folks!

After accosting one of my friends for some help with a query, he's pointed me in this direction, and while I see ALOT of invaluble information here, I'm struggling to find a solution explained in laymans terms for a fool such as myself! While I have seen various answers to this quesiton, I havn't understood the process or how the final result has been reached..

I'm hoping some of you knowledgable chaps would take pitty and find some time to explain to me exsactly what I need to change in my query to calculate "Working Days", perhaps I'm taking this from the wrong direction entirely.
----------------------------------------------------------------------------------------------------------------------------------------------


I have the following fields:

• [Name] - Text Field - Taken from an ODBC linked table
• [NEW4WelcomeL] - Short Date Field - Taken from an ODBC linked table
• [NEW3InitialC] - Short Date Field - Taken from an ODBC linked table
• [TargetDate] - Short Date Field
• [ActualDate] - Short Date Field

[Name] - The company name
[New4WelcomeL] - Date that a welcome letter is sent out
[New3InitialC] - Date that Initial Contact for Policy Audit is made
[TargetDate] - A usermade field that does this calculation: [New4WelcomeL]+5
[ActualDate] - A usermade field that does this calculation: [TargetDate]-[NewInitialC]


----------------------------------------------------------------------------------------------------------------------------------------------
[New3InitialC] needs to take place within 5 working days of [New4WelcomeL], if it doesn't I'd like to see how many days it has run over by.

As you can see my very basic query is flawed from the getgo when it comes to weekends, any ideas?

Many thanks, in advance!

Regards - Maynard
Lost In Access 2007

 

[SimonB1978]

Hi,

I'vo got these two functions which might help you out. Thing is they are coded in French so you might need to translate to something more meaningful to you.

Jour = Day, Ouvrable= Working, Delai = Delay, Debut = Start

Public Function JourOuvrable(dtDate As Date) As Boolean
    Dim intJour As Integer
 
    intJour = Weekday(dtDate)
 
    If intJour >= vbMonday And intJour <= vbFriday Then
        JourOuvrable = True
    Else
        JourOuvrable = False
    End If
 
End Function
Public Function DelaiOuvrable(DateDepart As Date, NbJours As Integer) As Date
 
    Dim intX As Integer
 
    DelaiOuvrable = DateDepart
 
    For intX = 1 To NbJours
 
        DelaiOuvrable = DelaiOuvrable + 1
 
        While Not JourOuvrable(DelaiOuvrable)
            DelaiOuvrable = DelaiOuvrable + 1
        Wend
    Next
 
End Function

You could then use the DelaiOuvrable([New3InitialC], 5) and compare that to [New4WelcomeL]

Hope that can help,

Simon B.