Results 1 to 5 of 5
  1. #1
    habiler is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2017
    Posts
    13

    Problem with DSum

    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

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dsum function:
    DSUM( [field to sum], [table/query], [where clause])

    you have: "DateDiff('m', [debut], [fin])" as a field.
    Not a field.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Do you want days or months in the Datediff? Your example data is days but in your calculation you are using "m" months. Change to "d" if you want days.

    The below select statement might work, put it into a query (update field or table names if needed) and run it, see if it gives you the total you are looking for.

    SELECT Sum(DateDiff("d",[Debut],[fin])) AS vCalc
    FROM Decision
    WHERE ((([Decision].Nature)="Nature") And (([Decision].Matr)="Matr"));

  4. #4
    habiler is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2017
    Posts
    13
    Hello Everybody.

    My totMois has a zero value. Where is my error please.


    Habiler

    Code:
    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

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    - totMois should be a Double, not a Variant
    - add a line of code: Debug.Print DateDiff('m', [debut], [fin]) & " matr " & matr & " Nature " & nature
    - see what each value contains and compare it to the record on the table

Please reply to this thread with any new information or opinions.

Similar Threads

  1. problem with dsum
    By question mark in forum Forms
    Replies: 1
    Last Post: 01-24-2017, 04:25 PM
  2. DSum problem.
    By kowalski in forum Access
    Replies: 1
    Last Post: 10-26-2012, 05:24 PM
  3. Dsum problem help?
    By manos39 in forum Forms
    Replies: 0
    Last Post: 01-12-2012, 05:53 AM
  4. Dsum criteria problem
    By leonhuynh2006 in forum Queries
    Replies: 3
    Last Post: 10-04-2011, 03:18 AM
  5. DSum problem, please help!!
    By Chissy in forum Queries
    Replies: 3
    Last Post: 08-10-2011, 07:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums