Problem with Dsum

[habiler]

Hello,

How can store the total of "DateDiff('m', [debut], [fin])" of a [Nature] by [Matr].
.
I have a table with fields debut, fin, Matr, Nature.
Matr Nature Debut fin Calculation (DateDiff('m', [debut], [fin])")
3478 Holiday 1/1/2017 5/1/2017 5 days
3478 Holiday 1/4/2017 3/4/2017 3 days
3478 Seek 1/5/2017 1/5/2017 1 days

What i will have :

Matr Nature ML

3478 Holidays 8
3478 Seek 1

CODE
Function ML(Matr As Long, Nature As String, debut As Date, fin As Date, fonction As Long) As String
 
  Dim totAbs As Long
  Dim totMois As Integer
 
   totMois = Nz(DateDiff("m", [debut], [fin]))
 
   totAbs = DSum("DateDiff('m', [debut], [fin])", "Decisions", "'[Matr]= ' & 'Matr' And '[Nature]= ' & 'Nature'")
 
    If fonction = 1210 Or fonction = 1101 Then
        ML = totMois
    Else
        ML = totAbs
    End If
 
End Function

​

​

 

[plog]

You normally don't store values in a table, instead you calculate them when you need them. Why do you want to store them?

Also, what happens when Fin or Debut changes? Should the calculation as well?

 

[MarkK]

Hi, try SQL like this...

SELECT Matr, Nature, Sum(DateDiff('m', [debut], [fin])) As ML
FROM VotreTable
GROUP BY Matr, Nature

hth
Mark

 

[habiler]

Hello to all,

The value of totMois indicates a zero value. Where is my error.

Thanks

Habiler

Function ML(Matr As Long, Nature As String, debut As Date, fin As Date, fonction As Integer, Pourcent As Integer) As Integer

  Dim totAbs As Long
  Dim totMois As Variant
  Dim stockAbstype As Integer
  
  

  If [debut] = [fin] Then
  totMois = 1
  Else
     totMois = Nz(DSum("DateDiff('m', [debut], [fin])", "Decisions", "[Matr]= " & Matr & " AND [Nature]= '" & Nature & "'"))
    End If
    
     If IsNull(totMois) Then
     Response = acDataErrContinue
     MsgBox "???"
     Else: Response = acErrDataAdded
     End If
     
    If Nature = 31 Then            '1
       If Pourcent = 80 Then       '2
        ML = totMois / 5
    Else
    Pourcent = 50
        ML = totMois / 2
    End If                         '2

    stockAbstype = ML
    If stockAbstype = 4 Then
    ML = "Exhausted"
    End If
    
        End If                     '1
        
        
End Function

 

[Minty]

Cross posted here: http://www.utteraccess.com/forum/index.php?showtopic=2045392&hl=

OP - Have a Read here https://www.excelguru.ca/content.php?184

 

[arnelgp]

Lol, you also xposted. Xposted the waning here and to utter access. You cannot serve two master, either you will hate the other or love the other.

 

[Minty]

Lol, you also xposted. Xposted the waning here and to utter access. You cannot serve two master, either you will hate the other or love the other.

I only pointed out the cross post - which quite a lot of contributors find very annoying. I do the same on stackoverflow. No biggie.

 

[arnelgp]

add another private function on your code:

Function ML(Matr As Long, Nature As String, debut As Date, fin As Date, fonction As Long, Pourcent As Long) As Variant
    Dim totAbs As Long
    Dim totMois As Long
    Dim stockAbstype As Long
    
    If debut = fin Then
        totMois = 1
    Else
        totMois = SumDateDiff(Matr, Nature)
        If Nature = 31 Then
            If Pourcent = 80 Then
                ML = totMois / 5
            Else
                Pourcent = 50
                ML = totMois / 2
            End If
            
            stockAbstype = ML
            If stockAbstype = 4 Then
                ML = "Exhausted"
            End If
        End If
    End If
End Function

Private Function SumDateDiff(Matr, Nature) As Long

    Dim rs As DAO.Recordset
    
    Set rs = DBEngine(0)(0).OpenRecordset("Select debut, fin From Decisions " & _
                    "Where Matr =" & Matr & " And Nature = '" & Nature & "'")
    With rs
        If Not (.EOF And .BOF) Then .MoveFirst
        While Not .EOF
            SumDateDiff = SumDateDiff + DateDiff("m", Nz(.Fields("debut"), 0), Nz(.Fields("fin"), 0))
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
End Function

 

[habiler]

Now it gives me an error 3061 too few parameters : Expecterd 1.

I have checked the spelling and the fields but all seems ok

Thanks

Habiler

 

[habiler]

At level

 Set rs = DBEngine(0)(0).OpenRecordset("Select debut, fin From Decisions " & _
                    "Where Matr =" & Matr & " And Nature = '" & Nature & "'")

 

[arnelgp]

create a breakpoint at ML function
(click inside the function and press F9)
now, run your form or query.
press F8 to step to the code.
hover to your cursor the Matr and Nature variable.
What are their value?

 

[habiler]

The value of Matr or Nature are Ok but the value of rs is nothing.

 

[arnelgp]

Check if my field name and table name are correct

 

[arnelgp]

Check if my field name and table name are correct

 

[habiler]

Yes all is OK