Max in reports

[Danielf]

In a text box - in the report footer -I have put the following code for my control source :

=Max([mytable]![myfield])

but when i am previewing the report, I am receiving the message :"enter parameter value
for mytable!myfield.

Thanks for help

Daniel

 

[Travis]

This is because Access cannot determine what field ([mytable]![myfield]) is and where to get it.

Reports work just a little differently then forms in that in order to use a field that is part of the ControlSource you will need to have it on the report. But in your case it looks as if what you want is to retrieve the Largest/Greatest value from a Table Mytable for Field Myfield:

Try this

Set the control Source of the field = to this:

=GetMax()

Then add this function to your report:

Private Function GetMax() as Variant
On Error Goto ErrorHandler
GetMax = CurrentDb.OpenRecordset("Select Max([MyField]) from [MyTable]").Field(0)

Exit Function
ErrorHandler:
msgbox Err.Description

End Function

 

[Danielf]

Hi Travis,

Thanks very much, it is working
(I just had to change ".Field(0)" in
".Fields(0)"

Thanks again, I will now perform all my
calculations in my reports with this method.

Daniel