sum my records 1

[CompGirl]

hello everyone

this is the code behind my report, the only thing that i want to do is to sum the IDs that pop up ..

Private bContinuedSection As Boolean, sLastHeaderKey As Variant
Private Function IsContinuedSection() As Boolean
IsContinuedSection = bContinuedSection
End Function

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Re As DAO.Recordset
Dim Envi_Risk_Class, Risk
Set Re = CurrentDb.OpenRecordset("SELECT Envi_Risk_Class, HazardID FROM tblHazard Where Envi_Risk_Class ='" & Me!hiddenEnvi_Risk_Class & "' ORDER BY HazardID&quot
Risk = ""
Me!Risker = Me!hiddenEnvi_Risk_Class
Do While Not Re.EOF
Risk = Risk & Re!HazardID & ", "
Re.MoveNext
Loop
Me!ID = Left(Risk, Len(Risk) - 2)
End Sub

can someone give me an idea? thx

 

[tty0]

you could requery with an SQL SUM rather than doing a loop
i.e.

Set Re = CurrentDb.OpenRecordset("SELECT SUM(HazardID) as HazardTotal FROM tblHazard Where Envi_Risk_Class ='" & Me!hiddenEnvi_Risk_Class & "')

total = Re("HazardTotal&quot



'mi casa es su casa'
]-=tty0=-[

 

[CompGirl]

but i want to keep the ids.. and just add another box that prints out total...? how am i supposed to do then

 

[tty0]

ok to keep it all seperate if there is a text box called txtSumTotal you could put this in the sub, so you dont use the same recordset:

Dim ReSum As DAO.Recordset

Set ReSum = CurrentDb.OpenRecordset("SELECT SUM(HazardID) as HazardTotal FROM tblHazard Where Envi_Risk_Class ='" & Me!hiddenEnvi_Risk_Class & "')

txtSumTotal.text = ReSum("HazardTotal&quot

'mi casa es su casa'
]-=tty0=-[

 

[CompGirl]

hmmm it seems right but its complaining on that.. it wont work :/

 

[CompGirl]

i dont understand where the compiling error is

 

[tty0]

what error is it giving you?

'mi casa es su casa'
]-=tty0=-[

 

[CompGirl]

list seperator or )

either im stupid or blinde

 

[CompGirl]

thx.. it was nothing.. like i said i was blinde thx for ya time

 

[tty0]

Where is the break? is it on the text box line?

ReSum("HazardTotal&quot

if so try this instead

ReSum!HazardTotal



'mi casa es su casa'
]-=tty0=-[

 

[vbkris]

Set Re = CurrentDb.OpenRecordset("SELECT Envi_Risk_Class, HazardID FROM tblHazard Where Envi_Risk_Class ='" & Me!hiddenEnvi_Risk_Class & "' ORDER BY HazardID&quot
Risk = ""
Me!Risker = Me!hiddenEnvi_Risk_Class
tot=0
Do While Not Re.EOF
Risk = Risk & Re!HazardID & ", "
tot=tot+cint(Re!HazardID)
Re.MoveNext
Loop
Me!ID = Left(Risk, Len(Risk) - 2)
msgbox tot


Known is handfull, Unknown is worldfull

 

[CompGirl]

tty0.. i meant i got it to work.. so thx

vbkris... hmmm that was a strange one.. message boxes popping up giving me strange values and then nothing on the report

 

[tty0]

your welcome

anytime

'mi casa es su casa'
]-=tty0=-[

 

[vbkris]

msgbox tot is ur total, u have to take it to ur report....

Known is handfull, Unknown is worldfull