UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Probem With Dsum, Access 2010    
 
   
RAZMaddaz
post Sep 16 2017, 03:13 PM
Post#21


UtterAccess VIP
Posts: 9,516
Joined: 23-May 05
From: Bethesda, MD USA


ha idler,

Can you please try making a new Query, with the two Fields (Debut and Fin), then use the DateDiff function and see if you can get the new Query to work with the correct calculation that you are looking for?

Understand?

Thanks.

RAZMaddaz
Go to the top of the page
 
Doug Steele
post Sep 16 2017, 03:32 PM
Post#22


UtterAccess VIP
Posts: 21,312
Joined: 8-January 07
From: St. Catharines, ON (Canada)


For debugging purposes, try changing your function to

CODE
Private Function SumDateDiff(Matr, Nature) As Long
  
Dim rs As DAO.Recordset
Dim strSQL As String
    
    strSQL = "Select debut, fin From Decisions " & _
        "Where Matr =" & Matr & " And Nature = '" & Nature & "'"

    Debug.Print "strSQL = " & strSQL
  
    Set rs = CurrentDb.OpenRecordset(strSQL)
    With rs
        If Not (.EOF And .BOF) Then .MoveFirst
        While Not .EOF
            Debug.Print "Debut = " & Nz(.Fields("debut"), 0) & ", Fin = " & Nz(.Fields("fin"), 0) & _
                ", Calculation = " & DateDiff("d", Nz(.Fields("debut"), 0), Nz(.Fields("fin"), 0))
            SumDateDiff = SumDateDiff + DateDiff("d", Nz(.Fields("debut"), 0), Nz(.Fields("fin"), 0))
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
  
End Function


Do you get reasonable numbers printing out from the recordset? If you don't get any records written out, does the SQL that's printed actually return any records when you try running it manually?
Do you ge

--------------------
Go to the top of the page
 
habiler
post Sep 16 2017, 04:41 PM
Post#23



Posts: 40
Joined: 6-August 15



Here is a zipped DB.

Thanks for all.

Habiler
Attached File(s)
Attached File  test.zip ( 844.13K )Number of downloads: 1
 
Go to the top of the page
 
Doug Steele
post Sep 16 2017, 05:06 PM
Post#24


UtterAccess VIP
Posts: 21,312
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Your table Decisions doesn't even have a field named Nature in it. As a result, none of your code is going to work!

--------------------
Go to the top of the page
 
habiler
post Sep 17 2017, 07:40 AM
Post#25



Posts: 40
Joined: 6-August 15



Very sorry for this stupid error.

I replaced the SQL by
CODE
Set rs = CurrentDb.OpenRecordset("Select debut, fin From Decisions INNER JOIN typedossier ON Decisions.abstype = typedossier.Nature Where Matr =" & Matr & " And Nature = '" & Nature & "'")

But now i have an error 13 type mismatch.
Go to the top of the page
 
Doug Steele
post Sep 17 2017, 09:04 AM
Post#26


UtterAccess VIP
Posts: 21,312
Joined: 8-January 07
From: St. Catharines, ON (Canada)


As I suggested earlier, add some statements in your function to help you with your debugging.

CODE
Dim rs As DAO.Recordset
Dim strSQL As String
    
    strSQL = "Select debut, fin From Decisions INNER JOIN typedossier ON Decisions.abstype = typedossier.Nature " & _
        "Where Matr =" & Matr & " And Nature = '" & Nature & "'"
  
    Debug.Print "strSQL = " & strSQL
  
    Set rs = CurrentDb.OpenRecordset(strSQL)

I'm especially interested in seeing what the SQL that's being passed to the OpenRecordset statement looks like. Try running that same SQL as a query, and see what you get. (Usually you'll get a more helpful error message that way...)

--------------------
Go to the top of the page
 
habiler
post Sep 17 2017, 11:50 AM
Post#27



Posts: 40
Joined: 6-August 15



That's what the debug1.print gives + the compile gives me error 13 type mismatch
CODE
strSQL = Select debut, fin From Decisions INNER JOIN typedossier ON Decisions.abstype = typedossier.Nature Where Matr =3018 And Nature = '31'
Go to the top of the page
 
Doug Steele
post Sep 17 2017, 12:19 PM
Post#28


UtterAccess VIP
Posts: 21,312
Joined: 8-January 07
From: St. Catharines, ON (Canada)


So does it run correctly when you create a query and set it to that SQL string?

Where does the error actually occur in the function? On Set rs = CurrentDb.OpenRecordset(strSQL) or on SumDateDiff = SumDateDiff + DateDiff("d", Nz(.Fields("debut"), 0), Nz(.Fields("fin"), 0))?

--------------------
Go to the top of the page
 
habiler
post Sep 17 2017, 02:36 PM
Post#29



Posts: 40
Joined: 6-August 15



When i run my query date mois i have o lot of error
Attached File(s)
Attached File  test.zip ( 871.33K )Number of downloads: 2
 
Go to the top of the page
 
Doug Steele
post Sep 17 2017, 04:59 PM
Post#30


UtterAccess VIP
Posts: 21,312
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Your query date mois calls functions f_ddp and ML, neither of which you've talked about before, so it's extremely difficult for me to comment.

Let's step back a moment.

Describe your data, and describe what you're trying to do, please.

So far, you've been asking questions in isolation, without explaining what it is you're trying to do (and why). Once we know that, perhaps we can give you a better answer.



--------------------
Go to the top of the page
 
habiler
post Sep 18 2017, 12:24 AM
Post#31



Posts: 40
Joined: 6-August 15



What I try to do is to have the sum of all the months (SumDateDiff = [debut(= startdate]], [fin (= endDate)]) that a person (Matr) have taken during his career for a precise leave or decreased activity (([Nature] and [pourcent]).

debut and are date fields
Nature is a text field
Matr and pourcent are Number(s) double
Go to the top of the page
 
habiler
post Sep 18 2017, 05:34 AM
Post#32



Posts: 40
Joined: 6-August 15



Hello,

It's seems working.

I verify

HAbiler
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2017 - 03:41 PM