Discussion :

[ilank]

Viggen0020, commence par une requête plus simple pour voir où ça cloche

ex:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 SELECT Ref_W,DateEnregistrementMSX,JourOuvres(DateEnregistrmentMSX,Date()) As [Nb jours ouvrés]
 FROM table_unique;

Puisque tu as retiré le -1 remplace < par <= dans cette ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Loop While DateDeb < DateFin

[Viggen0020]

Bonjour,

J'ai finalement réussi en utilisant la fonction:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Public Function JourOuvres(ByVal date1 As Date, ByVal Date2 As Date) As Long
    Dim DateDeb As Date, DateFin As Date
    If IsNull(date1) Or IsNull(Date2) Then GoTo JourOuvres_Erreur
    If Not IsDate(date1) Or Not IsDate(Date2) Then GoTo JourOuvres_Erreur
    date1 = DateSerial(Year(date1), Month(date1), Day(date1))
    Date2 = DateSerial(Year(Date2), Month(Date2), Day(Date2))
    If date1 = Date2 Then GoTo JourOuvres_Erreur
    DateDeb = date1
    DateFin = Date2
    If date1 > Date2 Then
        DateDeb = Date2
        DateFin = date1
    End If
    JourOuvres = CLng(DateFin) - CLng(DateDeb)
    If JourOuvres = 0 Then Exit Function
    DateDeb = DateDeb + 1
    Do
        If (Weekday(DateDeb, vbMonday) >= 6) Or (JourFérié(DateDeb) = True) Then JourOuvres = JourOuvres - 1
        DateDeb = DateDeb + 1
    Loop While DateDeb <= DateFin
    Exit Function
JourOuvres_Erreur:
 JourOuvres = 0
End Function

dans la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT JourOuvres([DateEnregistrementMSX],Date())-[type_document]![délai]-JourOuvres((IIf([table_unique]![DDC-delai_1-fin] Is Null,0,[table_unique]![DDC-delai_1-fin])),(IIf([table_unique]![DDC-delai_1-debut] Is Null,0,[table_unique]![DDC-delai_1-debut])))-JourOuvres((IIf([table_unique]![DDC-delai_2-fin] Is Null,0,[table_unique]![DDC-delai_2-fin])),(IIf([table_unique]![DDC-delai_2-debut] Is Null,0,[table_unique]![DDC-delai_2-debut])))-JourOuvres((IIf([table_unique]![DDC-delai_3-fin] Is Null,0,[table_unique]![DDC-delai_3-fin])),(IIf([table_unique]![DDC-delai_3-debut] Is Null,0,[table_unique]![DDC-delai_3-debut]))) AS depassement, table_unique.Ref_W, table_unique.TypeDocument, table_unique.DateEnregistrementMSX, IIf([table_unique]![DDC-delai_1-debut] Is Null,0,[table_unique]![DDC-delai_1-debut]) AS [DDC1-debut], IIf([table_unique]![DDC-delai_1-fin] Is Null,0,[table_unique]![DDC-delai_1-fin]) AS [DDC1-fin], IIf([table_unique]![DDC-delai_2-debut] Is Null,0,[table_unique]![DDC-delai_2-debut]) AS [DDC2-debut], IIf([table_unique]![DDC-delai_2-fin] Is Null,0,[table_unique]![DDC-delai_2-fin]) AS [DDC2-fin], IIf([table_unique]![DDC-delai_3-debut] Is Null,0,[table_unique]![DDC-delai_3-debut]) AS [DDC3-debut], IIf([table_unique]![DDC-delai_3-fin] Is Null,0,[table_unique]![DDC-delai_3-fin]) AS [DDC3-fin]
FROM table_unique INNER JOIN type_document ON table_unique.TypeDocument = type_document.type_document
WHERE (((table_unique.DateEnregistrementMSX)>Now()-60) AND ((IIf([table_unique]![DDC-delai_1-debut] Is Null,0,[table_unique]![DDC-delai_1-debut]))<>0) AND ((IIf([table_unique]![DDC-delai_1-fin] Is Null,0,[table_unique]![DDC-delai_1-fin]))<>0) AND ((IIf([table_unique]![DDC-delai_2-debut] Is Null,0,[table_unique]![DDC-delai_2-debut]))=0) AND ((IIf([table_unique]![DDC-delai_2-fin] Is Null,0,[table_unique]![DDC-delai_2-fin]))=0) AND ((IIf([table_unique]![DDC-delai_3-debut] Is Null,0,[table_unique]![DDC-delai_3-debut]))=0) AND ((IIf([table_unique]![DDC-delai_3-fin] Is Null,0,[table_unique]![DDC-delai_3-fin]))=0) AND ((table_unique.[FO-BO])="FO") AND ((table_unique.DateTraitement) Is Null)) OR (((table_unique.DateEnregistrementMSX)>Now()-60) AND ((IIf([table_unique]![DDC-delai_1-debut] Is Null,0,[table_unique]![DDC-delai_1-debut]))=0) AND ((IIf([table_unique]![DDC-delai_1-fin] Is Null,0,[table_unique]![DDC-delai_1-fin]))=0) AND ((IIf([table_unique]![DDC-delai_2-debut] Is Null,0,[table_unique]![DDC-delai_2-debut]))=0) AND ((IIf([table_unique]![DDC-delai_2-fin] Is Null,0,[table_unique]![DDC-delai_2-fin]))=0) AND ((IIf([table_unique]![DDC-delai_3-debut] Is Null,0,[table_unique]![DDC-delai_3-debut]))=0) AND ((IIf([table_unique]![DDC-delai_3-fin] Is Null,0,[table_unique]![DDC-delai_3-fin]))=0) AND ((table_unique.[FO-BO])="FO") AND ((table_unique.DateTraitement) Is Null)) OR (((table_unique.DateEnregistrementMSX)>Now()-60) AND ((IIf([table_unique]![DDC-delai_1-debut] Is Null,0,[table_unique]![DDC-delai_1-debut]))<>0) AND ((IIf([table_unique]![DDC-delai_1-fin] Is Null,0,[table_unique]![DDC-delai_1-fin]))<>0) AND ((IIf([table_unique]![DDC-delai_2-debut] Is Null,0,[table_unique]![DDC-delai_2-debut]))<>0) AND ((IIf([table_unique]![DDC-delai_2-fin] Is Null,0,[table_unique]![DDC-delai_2-fin]))<>0) AND ((IIf([table_unique]![DDC-delai_3-debut] Is Null,0,[table_unique]![DDC-delai_3-debut]))=0) AND ((IIf([table_unique]![DDC-delai_3-fin] Is Null,0,[table_unique]![DDC-delai_3-fin]))=0) AND ((table_unique.[FO-BO])="FO") AND ((table_unique.DateTraitement) Is Null)) OR (((table_unique.DateEnregistrementMSX)>Now()-60) AND ((IIf([table_unique]![DDC-delai_1-debut] Is Null,0,[table_unique]![DDC-delai_1-debut]))<>0) AND ((IIf([table_unique]![DDC-delai_1-fin] Is Null,0,[table_unique]![DDC-delai_1-fin]))<>0) AND ((IIf([table_unique]![DDC-delai_2-debut] Is Null,0,[table_unique]![DDC-delai_2-debut]))<>0) AND ((IIf([table_unique]![DDC-delai_2-fin] Is Null,0,[table_unique]![DDC-delai_2-fin]))<>0) AND ((IIf([table_unique]![DDC-delai_3-debut] Is Null,0,[table_unique]![DDC-delai_3-debut]))<>0) AND ((IIf([table_unique]![DDC-delai_3-fin] Is Null,0,[table_unique]![DDC-delai_3-fin]))<>0) AND ((table_unique.[FO-BO])="FO") AND ((table_unique.DateTraitement) Is Null))
ORDER BY JourOuvres([DateEnregistrementMSX],Date())-[type_document]![délai]-JourOuvres((IIf([table_unique]![DDC-delai_1-fin] Is Null,0,[table_unique]![DDC-delai_1-fin])),(IIf([table_unique]![DDC-delai_1-debut] Is Null,0,[table_unique]![DDC-delai_1-debut])))-JourOuvres((IIf([table_unique]![DDC-delai_2-fin] Is Null,0,[table_unique]![DDC-delai_2-fin])),(IIf([table_unique]![DDC-delai_2-debut] Is Null,0,[table_unique]![DDC-delai_2-debut])))-JourOuvres((IIf([table_unique]![DDC-delai_3-fin] Is Null,0,[table_unique]![DDC-delai_3-fin])),(IIf([table_unique]![DDC-delai_3-debut] Is Null,0,[table_unique]![DDC-delai_3-debut]))) DESC;

C'est un peu indigeste mais ca fonctionne !

Merci à tous !

Viggen