Form: OrderBy minimum of fields

[Henrich]

Hello,

I have a table (Access 2003) which contains among others three date fields D1, D2, D3. I have a form
showing one record at a time; for the sequence of the records, I set the OrderBy property
of the form with VBA. I want the records to be sorted according to the oldest of the three dates.

What works is:

Me.OrderBy = "D1,D2,D3"
Me.OrderByOn = True

This, however, sorts first by D1, then by D2, then by D3. What I want, is sorting
by the minimum of the three dates, so that the records where one of them is old appear first:

Me.OrderBy = "Min(D1,D2,D3)"

But this doesn't work. Firstly there is no Min function that compares fields within
one record, but every attempt to a workaround fails since apparently no expressions or
functions at all can be used in the OrderBy property of the form. If I put e.g.

Me.OrderBy = "Fix(D2)"

then on opening the form a pop-up dialog appears: "Enter parameter value: 'Fix(D2)' ", so
appearently the expression is not recognized.

I also do some filtering. Here I use:

Me.Filter = "((D2<=Now()) AND (F2=0)) OR ((D3<=Now()) AND (F3=0))"
Me.FilterOn = True

This works, so some complex expression can be used at least for filtering; but apparently
not for ordering.

Any idea how I can get the ordering working?

Thanks

Henrich

 

[Henrich]

Solution

I have now found a solution. Expressions are (apparently) not allowed in the OrderBy property of Access forms, but in the OrderBy clause of queries. So I have changed the form's RecordSource from the table to:

Me.RecordSource = "SELECT * FROM Tabelle ORDER BY Hmin(D1,D2,D3);"
Me.Requery

where I use a user-defined function Hmin (placed in a new module of the database, not of the form) which calculates the minimum of several input arguments.