Code in report footer to highlight total background & totals calculation

[Hugh Self Taught]

Hi Gurus,

I'm trying to create a report in my project to replicate one from the access database I'm rewriting in VB.net

In that report I had the following code in the relevant group footer which calculated the total of the current (highest) level a couple has earned & highlighted it if it was => max value for the level.

I've created another crystal report to simply list competitions captured in the database so can do a simple report but this is far more involved. Any advice on how i get to do this?

i = StylID

intMaxGrade = Nz(DMax("Disp_Seq", "qryMaxGrade", "PtsMale =" & intMaleID & "And PtsFemale =" & intFemID & "And StylID =" & i & "And Disp_Seq < " & 9), 0) 'Get highest grade achieved

If intMaxGrade > 5 Then ' if above Gold ie: Dress Codes
If intMaxGrade = 8 Then
intMaxGrade = Nz(DMax("Disp_Seq", "qryMaxGrade", "PtsMale =" & intMaleID & "And PtsFemale =" & intFemID & "And StylID =" & i & "And Disp_Seq between " & 6 & " And " & 7), 0) 'Get highest non RS grade achieved
End If

intGrade6Points = DLookup("PointsToProgress", "[Dance Section]", "Disp_Seq =" & 6)
intGrade7Points = DLookup("PointsToProgress", "[Dance Section]", "Disp_Seq =" & 7)

Set rsTempHistData = CurrentDb.OpenRecordset("qryTempHistData") 'Load dataset of all Male & Female partnerships
rsTempHistData.MoveLast
rsTempHistData.MoveFirst

intChkGrade1 = 0
intChkGrade2 = 0

Do Until rsTempHistData.EOF()
If rsTempHistData.Fields("Disp_Seq") > 5 And rsTempHistData.Fields("Style") = 1 Then
intCalcTotal1 = intCalcTotal1 + Nz(rsTempHistData.Fields("PtsPoints"), 0) + Nz(rsTempHistData.Fields("PtsPointsBonus"), 0)

If intMaxGrade = 0 And rsTempHistData.Fields("Disp_Seq") = 8 Then
If intChkGrade1 <> 7 Then
If intCalcTotal1 >= intGrade6Points Then
intChkGrade1 = 7
RowTotal1 = intCalcTotal1
intCalcTotal1 = 0
Else
RowTotal1 = intCalcTotal1
End If
Else
RowTotal1 = intCalcTotal1
End If

ElseIf intMaxGrade = 6 And rsTempHistData.Fields("Disp_Seq") = 6 Or intMaxGrade = 6 And rsTempHistData.Fields("Disp_Seq") = 8 Then
RowTotal1 = intCalcTotal1
If intChkGrade1 = 7 And rsTempHistData.Fields("Disp_Seq") = 6 Then
intCalcTotal1 = intCalcTotal1 + intPrevTotal1
RowTotal1 = intCalcTotal1
intChkGrade1 = 0
blnPromoted1 = False

ElseIf intChkGrade1 <> 7 Then
If intCalcTotal1 >= intGrade6Points Then
intChkGrade1 = 7
intPrevTotal1 = intCalcTotal1
intCalcTotal1 = 0
blnPromoted1 = True
End If
End If

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 8 Then
RowTotal1 = 0

If intChkGrade1 = 7 Then
RowTotal1 = intCalcTotal1

ElseIf intChkGrade1 <> 7 Then
If intCalcTotal1 >= intGrade6Points Then
intChkGrade1 = 7
intCalcTotal1 = 0
End If
End If

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 7 Then
If RowTotal1 = 0 And intChkGrade1 = 6 Then
intCalcTotal1 = Nz(rsTempHistData.Fields("PtsPoints"), 0) + Nz(rsTempHistData.Fields("PtsPointsBonus"), 0)
End If

RowTotal1 = intCalcTotal1
intChkGrade1 = 7

ElseIf intMaxGrade = 7 And rsTempHistData.Fields("Disp_Seq") = 6 Then
RowTotal1 = 0
If intChkGrade1 = 7 Then
intCalcTotal1 = 0

ElseIf intChkGrade1 <> 7 Then
intChkGrade1 = 6
If intCalcTotal1 >= intGrade6Points Then
intChkGrade1 = 7
intCalcTotal1 = 0
End If
End If
End If

ElseIf rsTempHistData.Fields("Disp_Seq") > 5 And rsTempHistData.Fields("Style") = 2 Then
intCalcTotal2 = intCalcTotal2 + Nz(rsTempHistData.Fields("PtsPoints"), 0) + Nz(rsTempHistData.Fields("PtsPointsBonus"), 0)

Continue reading...