DSum problem

  • Thread starter Thread starter Christine Vollberg via AccessMonster.com
  • Start date Start date
C

Christine Vollberg via AccessMonster.com

hey all, need some help, I am trying to get a total sentence to print on a
report based on if the sentence is concurrent or consecutive. Example: If
a defendant is sentenced on 3 charges 2, 4, and 5 years and they are to be
consecutive then I want the total to show 11 years, if they are concurrent
I want the total to show 5 years in an unbound field on the report. Here
is my code but not working, will not show anything at all and gives me no
errors. I may just be putting in the wrong event.

Private Sub Form_AfterUpdate()
Dim TotalYears As Integer
If ConcurrentSentence = -1 Then
TotalYears = DSum("[Years]", "tblDefChargesSentence", "[CaseNo]= '" &
Me![CaseNo] & "' And [ChargeSeqNo] = '" & Me!ChargeSeqNo & "' And
[DefendantId]=" & Me![DefendantId])
Else
If ConsecutiveSentence = -1 Then
TotalYears = DSum("[Years]", "tblDefChargesSentence", "[CaseNo]= '" &
Me![CaseNo] & "' [DefendantId]=" & Me![DefendantId])
Else
TotalYears = 0
End If
End If
End Sub

Any help would be greatly appreciated. Thanks

Christine
 
If its concurrent you should use dmax not dsum. because if its 5,3,2 you want
5? dsum will return 10.
 
Put square brackets around your table ie dsum("[field]","
"... and take
the quotes away from fields that are numeric. ' are only needed for text
fields.
 
Private Sub Form_AfterUpdate()
Dim TotalYears As Integer

If ConcurrentSentence = -1 Then
TotalYears = DMax("[Years]", "[tblDefChargesSentence]", "[CaseNo]= '"
& Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId])

Else
If ConsecutiveSentence = -1 Then
TotalYears = DSum("[Years]", "[tblDefChargesSentence]", "[CaseNo]= '"
& Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId])

Else
TotalYears = 0

End If
End If
End Sub

This is what I did and still gives me nothing, in the fields I am trying to
total I have a 2, 4, 7 should give me 7. But gives me nothing
 
Are you sure the me![...] have values? You can put watches or breaks. Is
years numeric in your tables? Normal If logic is IF ... then ... Elseif ...
then ... else ... end if. I use true instead of -1 imho more readable.
 
I will change the -1 to true, the me! is in the subform attached to the
report so maybe I need to identify more explicitly on the location of the
date. Let me know what you think.
 
Private Sub Form_AfterUpdate()
Dim TotalYears As Integer

If ConcurrentSentence = -1 Then
TotalYears = DMax("[Years]", "[tblDefChargesSentence]", "[CaseNo]= '"
& Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId])

Else
If ConsecutiveSentence = -1 Then
TotalYears = DSum("[Years]", "[tblDefChargesSentence]", "[CaseNo]= '"
& Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId])

Else
TotalYears = 0

End If
End If
End Sub

This is what I did and still gives me nothing, in the fields I am trying to
total I have a 2, 4, 7 should give me 7. But gives me nothing
 
Back
Top