Get text of max in count of records ??

[luanhoxung]

Hi folks !
I tried to search all the postings from here to find out the answers
but not succeeded.
Here is my problem:
On my report, I have a Textbox which get the value of the result from
max of counting a group of records.
For example: 20 records with value "piece", 15 records with "tray", 10
records with "carton"-----> I need my Textbox show "piece".
I tried: Control source(my Textbox)
=IIf(Abs(Sum([Unit]='piece'))>Abs(Sum([Unit]='tray')),"Piece",Null).
But i have many Units like "cartons", "bags","portions",.... so i
think i cannot use a long syntax here.
I need a though or code for helping !
Thanks with appreciated !
Luan from VietNam

 

[Carl Rapson]

Hi folks !
I tried to search all the postings from here to find out the answers
but not succeeded.
Here is my problem:
On my report, I have a Textbox which get the value of the result from
max of counting a group of records.
For example: 20 records with value "piece", 15 records with "tray", 10
records with "carton"-----> I need my Textbox show "piece".
I tried: Control source(my Textbox)
=IIf(Abs(Sum([Unit]='piece'))>Abs(Sum([Unit]='tray')),"Piece",Null).
But i have many Units like "cartons", "bags","portions",.... so i
think i cannot use a long syntax here.
I need a though or code for helping !
Thanks with appreciated !
Luan from VietNam

You could create a function to do the determination, returning the
appropriate value. Then reference that function in your Control Source:

=MyFunction()

The function won't be tied to your report's Record Source, so you'll need to
use either a recordset or DSum calls to do the work.

Carl Rapson