Count and sum data in report

[Jayce72]

Hopefully an easy one.

For example, I have some data and in this data contains a column with fruit.

Four rows have apples
Two rows have pears
Five rows have oranges

In the header I want to say there where 3 fruits, but if I use count it says 11 (which is of course right - but not what I want it to do)

Any help please

 

[sxschech]

Depending on how you want the report to look you could add a group by fruit under the sorting and grouping section or try dcount and use criteria to count up the fruit type

 

[Jayce72]

Depending on how you want the report to look you could add a group by fruit under the sorting and grouping section or try dcount and use criteria to count up the fruit type

I don't think I can use the Dcount as there maybe more fruits than the ones I know, unless I am misunderstanding the DCount function.

 

[JHB]

You could use a recordset with a "SELECT DISTINCT ..." in the query string. The RecordCount would give you how many different fruit there are.
Put the code it in the Open event.

 

[arnelgp]

now code your report's Load event:

Private Sub Report_Load()
Dim strsql As String
Dim rs As Dao.Recordset
strsql = "select count(*) from (select distinct [fieldname] from " & Me.RecordSource & ")"
Set rs = DBEngine(0)(0).OpenRecordset(strsql)
Me.TextFruitCount = rs(0)
rs.Close
Set rs = Nothing
End Sub