Highlight greatest field in report

[joepineapples]

Hi All

I have a report that has several fields on age: 0-5, 6-10, 11-13 etc. What I would like is for the field with the greatest value to be highlighted in the report.

Is there a simple way to do this?

Many Thanks

JP

 

[Atomic Shrimp]

I don't understand the question...

Are those age ranges text value possibilities all in the same column? Are they populated from a lookup table, or are they just text?

Is there a possibility that more than one row could share the same (maximum) value? What do you want to see on your report if that happens?

 

[joepineapples]

Sorry Atomic Shrimp - I'll be more explicit.

These are numeric fields.

I have a form that collects data on the age of individuals. If for example they are aged 4 then they would enter a value of 1 in the 0-5 field, if they were 10 they would add a value of 1 in the 6-10 field ... and so on and so forth up until the age of 25. It is essentially a counting tool on service user ages - these figures are then counted in a query for a report.

When I run the report I want it to highlight which field has the greatest value. So for example if the 0-5 field had 10 and the 6-10 had 9 I would want the 0-5 field highlighted. If there was more than one field with the greatest number then I would like both fields highlighted.

So essentially, what I am looking for is for a group of fields in a row to be compared to each other and the one(es) with the highest value to be highlighted.

Hopefully this makes sense?

 

[DCrake]

Using the minimum amount of code you would first create a union query that flips all the age bands into a vertical list, such as:

Select Band1 As Band, "0-5" As Title From Summary Where Band1 > 0

Union

Select Band2 As Band, "6-10" As Title From Summary Where Band2 > 0

Union

Select Band3 As Band, "11-15" As Title From Summary Where Band3 > 0

etc

Then Create a new query based on the above sorting by Band

Then in your report in the section that contains the actuals perform a DLookup on the sorted union query without any conditions (this will return the top record only)

Dim fld As String
Dim Amt As Integer

fld = DLookup("Title","QryUnionSorted")
Amt = DLookup("Band","QryUnionSorted")
Then code

Me(fld).FontBold = True

This will only work for the first one, if there are more than one with the same total you will have to adopt the code accordingly.

As you know the highest value you can then test each control in the report

If Me.Band1 = Amt Then Me.Band1.Fontbold = True
If Me.Band2 = Amt Then Me.Band2.Fontbold = True
If Me.Band3 = Amt Then Me.Band3.Fontbold = True
etc


David

 

[joepineapples]

Thanks David - I'll give this a try.