sorting on data calculated during DetailFormat?

[Howard]

I have a dynamic cross tab report displaying estimated and potental
student grades for a number of subjects.

In the DetailFormat event I use the contents of the est and pot grade
fields for each subject to count some stats that I then use that to set
the colour of the 'name' textbox. (shown arrowed below) This works fine.

However I now want to sort the report so that all the names in one
colour are in the same place (and then sorted alphabetically by name)

I can calulate a rank order from the counts I already calculate but how
do I make the report sort on it? I cannot alter the underlying query.


(essential code snippet below)

Howard

#################################################

For x = 1 To nColumns

{other stuff}

EstGrade = Nz(Me("text" & Format$(x - 1)), "null")
PotGrade = Nz(Me("text" & Format$(x)), "null")

'count num estimates above, below or equal to potentials
If MoreThan(EstGrade, PotGrade) Then
NumSubjectsAboveEst = NumSubjectsAboveEst + 1 <---------
Else
If Equal(EstGrade, PotGrade) Then
NumSubjectsEqualEst = NumSubjectsEqualEst + 1 <---------
Else
If LessThan(EstGrade, PotGrade) Then
NumSubjectsBelowEst = NumSubjectsBelowEst + 1 <-------
End If
End If

next x



'Now colour code the name

If (NumSubjectsBelowEst = 0) Then
If NumSubjectsAboveEst >= 2 Then Me("Namebox").BackColor = DarkGreen
If NumSubjectsAboveEst = 1 Then Me("Namebox").BackColor = LightGreen
Else
If NumSubjectsEqualEst > 0 Then
Me("Namebox").BackColor = DarkYellow
Else
If NumSubjectsBelowEst = 1 Then Me("Namebox").BackColor = Pink
If NumSubjectsBelowEst >= 2 Then Me("Namebox").BackColor = Red
End If
End If

 

[Marshall Barton]

I have a dynamic cross tab report displaying estimated and potental
student grades for a number of subjects.

In the DetailFormat event I use the contents of the est and pot grade
fields for each subject to count some stats that I then use that to set
the colour of the 'name' textbox. (shown arrowed below) This works fine.

However I now want to sort the report so that all the names in one
colour are in the same place (and then sorted alphabetically by name)

I can calulate a rank order from the counts I already calculate but how
do I make the report sort on it? I cannot alter the underlying query.

#################################################

For x = 1 To nColumns

{other stuff}

EstGrade = Nz(Me("text" & Format$(x - 1)), "null")
PotGrade = Nz(Me("text" & Format$(x)), "null")

'count num estimates above, below or equal to potentials
If MoreThan(EstGrade, PotGrade) Then
NumSubjectsAboveEst = NumSubjectsAboveEst + 1 <---------
Else
If Equal(EstGrade, PotGrade) Then
NumSubjectsEqualEst = NumSubjectsEqualEst + 1 <---------
Else
If LessThan(EstGrade, PotGrade) Then
NumSubjectsBelowEst = NumSubjectsBelowEst + 1 <-------
End If
End If

next x


'Now colour code the name

If (NumSubjectsBelowEst = 0) Then
If NumSubjectsAboveEst >= 2 Then Me("Namebox").BackColor = DarkGreen
If NumSubjectsAboveEst = 1 Then Me("Namebox").BackColor = LightGreen
Else
If NumSubjectsEqualEst > 0 Then
Me("Namebox").BackColor = DarkYellow
Else
If NumSubjectsBelowEst = 1 Then Me("Namebox").BackColor = Pink
If NumSubjectsBelowEst >= 2 Then Me("Namebox").BackColor = Red
End If
End If

A report's sorting can only be based on fields in the
report's record source or an expression (including user
defined functions) that uses record source fields.

If you can not change the query you are currently using but
you can change the report, I would think that you can set
the report's record source to a query or SQL statement (with
user defined functions) based on your existing query and do
the calculations there.