Sum alternate records (odd & even) (1 Viewer)

[Happy YN]

I would like to create a control on a section of a report whichwill sum all the odd records and another to sum all the even ones.
Any ideas?
Thanks

 

[Fizzio]

In a nutshell:

Create a functions, to sum your records eg

fnTotalAlternates (strOddEven as String)

Use a Select Case command to switch between odd/even.
Create a recordset clone of the forms recordset
step through every other record using the movenext method

On Odds - move to the first record initially
On Evens - move to the 2nd record initially.

Easy enough but get back if you struggle with the coding.

 

[Happy YN]

Woula appreciate the code if you could.It will take me ages of trial & error!
Thanks

 

[Fizzio]

OK DAO Version (Still getting to grips with ADO )

Public Function fnTotalAlternates (strOddEven as String) as Double

Dim MyDb as DAO.Database
Dim MyRs as DAO.Recordset


Set MyDb  = currentDb()
Set MyRs = MyDb.openrecordset(Your Recordsource)

Select Case strOddEven

Case "Odd"
do until MyRs.EOF
MyRs.movefirst
fnTotalAlternates = fnTotalAlternates + MyRS("FieldToAdd")
MyRs.moveNext
MyRs.moveNext
loop
exit function

Case "Even"
do until MyRs.EOF
MyRs.movefirst
MyRs.moveNext
fnTotalAlternates = fnTotalAlternates + MyRS("FieldToAdd")
MyRs.moveNext
MyRs.moveNext
loop
exit function

end select
Set MyRs = nothing
Set MyDb = nothing
end function

NB Not tested or debugged!
Also Odd will total records 0,2,4 etc as index starts at 0 despite it being the 1st record

Just to add. In the 2 separate controls set the ControlSource in the 'odd' one to =fnTotalAlternates("Odd") and likewise for the 'even' control.

 

[Happy YN]

OK thanks for this . Will try it out but will take me a few days
Thanks again