Maximum Function 1

[nayfeh]

Hi,

I have a function called RMax that obtains the MAXIMUM value of several fields in a table. I use this value in a query then a report is based on this query. The formula for one field is: [Total]/[RMax].

Since the maximum value is sometimes 0 , the total becomes a # ERROR in the report. This is okay when viewing it in access, but I get an error when exporting this report to an excel spreadsheet.

Is there a way around this so that the # ERROR doesn't show on the report? My function is below:
-------------------------
Function RMax(ParamArray FieldValues()) As Variant
Dim lngMax As Double
Dim varArg As Variant

lngMax = 0

For Each varArg In FieldValues
If varArg > lngMax Then
lngMax = varArg
End If
Next
RMax = lngMax

End Function

Thanks again!
TN

 

[WhiteZiggy]

Could u place and if at the end to make it 1 if its 0?

Because the [total] could never be more than the max right?

So 0/1 = 0 and no error??

 

[PaulBricker]

Or you could handle it like this
MyTotal:IIf([RMax]=0,[Total],[Total]/[Rmax])

Paul

 

[MichaelRed]

MyTotal:IIf([RMax]=0,[Total],[Total]/[Rmax])

is the correct approach (IIF) - but the WRONG answer. from my hazy math, the 'correct' answer is the max displayable value for the object, or nothing although some text (e.g. "Not Avaiable" | "N/A&quot are reasonable substitutes.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[nayfeh]

Thanks guys....worked like a charm!