Max Function in Report Footer 1

[SilentAiche]

All,

I have an Access 2002 table with six date fields that feed a report thru a query. The first and sixth date fields are manditory; the middle four may or may not be used.

In my report I am tracking the interval (# of days) between the six various dates. The interior of the report is fine; the problem is with Date6 since there might not be a Date5, in which the case the interval would between Date6 and Date4 (or 3, or 2 or 1). What I am looking for is this: [Date6] - max([date1],[date2],[date3] etc.), however, this use of "max" does not appear to be a valid function (yes, I've tried "maximum.")

Is the syntax wrong, or the whole notion? Is there some form of a max function that would work here? I am trying to avoid some IIf formula with five nested conditions.

Thanks to any and all for your thoughts.

 

[PHV]

Create your own Max function in a standard code module.
A starting point:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
If rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function
Then you can try this:
[Date6] - myMax([date1],[date2],...,[date5])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SilentAiche]

Dear PHV:

Your solution worked like a charm, with one very strange twist. When I entered [D6]-myMax([D5],[D4]...[D1]), it only produced results if D5 was not null. It completely ignored D1 thru D4. On a very strange hunch, I switched the order of the first and last date fields, i.e.,
[D6] - myMax([D1],[D4],[D3],[D2],[D5]). It makes absolutely no sense to me, but for some reason this works regardless of which fields may be null.

Thanks again!

 

[PHV]

Replace this:
If rv < Args(i) Then rv = Args(i)
By this:
If rv < Args(i) Or IsNull(rv) Then rv = Args(i)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SilentAiche]

Thanks again, PH. New code works regardless of what order the date fields are listed.

There was one other thing that happened that I forgot to mention. After I added your code module, when I ran the report I got this error message: "Your MS Access database or project contains a missing or broken reference to the file 'Vtext.dll' version 1.0." I click OK and it lets me keep going with no apparent ill effects.

Under Modules / Tools / References, there is no listing for Vtext.dll. However, there is an item (with its box checked) labeled "MISSING: Microsoft Voice Text." Are these related?

Am I running any significant risk operating without Vtext.dll?

Thanks for all your help.

 

[PHV]

I don't think my code as something to do with Microsoft Voice Text ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244