Minimum of multiple fields in expression (1 Viewer)

Ipem

Registered User.
Local time
Today, 15:24
Joined
Aug 26, 2013
Messages
29
Hello guys,

I am trying to set up a calculated field in one of my form querries, using expression builder.

In one of my source tables I have 4 date fields called Inspection date 1 to 4
I need the lowest date among those inspectinos which are in the future (next inspection) If all 4 dates are in the past or Nulls, the function should return the current date. The logic of the expression could be: MIN(MAX(D1,Date()),MAX(D2,Date()),MAX(D3,Date()),MAX(D4,Date())) How can I do this in expression builder?

The built-in functions DMin and Dmax work with single field arrays, witch would be perfect if the database were properly designed, but now I dont have the power to change this.

Suggestions?
 

plog

Banishment Pending
Local time
Today, 09:24
Joined
May 11, 2011
Messages
11,611
Why don't you have the power to change this now? The 'now' in that sentence makes it seem like a cop out.

What I would do is create a custom VBA function that takes 4 dates and returns the lowest one in the future, returning the the current date if non meet the criteria. In the query it would looke like this:

ClosestFutureDate: getClosestFutureDate([D1], [D2], [D3], [D4])

If this was an aggregate function and you needed to apply Min to that field you could put MIN() around the above code and change the Group By to Expression underneath it in Design view.

Here's the signature an a few lines of VBA code for the function:

Code:
Function getClosestFutureDate(dt1, dt2, dt3, dt4) AS Date
    ' takes 4 dates (dt1 - dt4) and returns lowest one occuring in future
 
ret = Date()
    ' will return current date if none of 4 dates meet criteria
 
If ((IsNull(dt1)=False) OR  (IsNull(dt2)=False) OR  (IsNull(dt3)=False) OR  (IsNull(dt4)=False))  
    ' if any of dates passed to function contain data, will see if they occur in future 
 
 
' more code needed here. 
 
    End If
 
getClosestFutureDate = ret
 
End Function

That should get you started.
 

Users who are viewing this thread

Top Bottom