IIF Statement to locate oldest date

[Vergy39]

I have a report with 4 columns that contain dates. I need to write an iif statement that will return the oldest date of the 4 clumns. I have tried =Min(expression), but this only allows 1 argument. I have 4 arguments. The column headers are TruePresOldestDate, BBBOldestDate, VerbalOldestDate, and CRLOldestDate. Any assistance is greatly appreciated.

Thanks
David V.

 

[boblarson]

What I would do is to use this function (others may have a better idea):

Function ReturnEarliestDate(strInput As String) As Date
    Dim varSplit As Variant
    Dim i As Integer
    Dim dteHold As Date
 
    varSplit = Split(strInput, "|", , vbTextCompare)
 
    dteHold = varSplit(i)
    For i = LBound(varSplit) To UBound(varSplit)
        If i > 0 Then
            If CDate(varSplit(i)) < dteHold Then
                dteHold = CDate(varSplit(i))
            End If
        End If
    Next i
 
    ReturnEarliestDate = dteHold
 
End Function

And to use it you would just pass your columns like this:


OldestDate:ReturnEarliestDate([TruePresOldestDate] & "|" & [BBBOldestDate] & "|" & [VerbalOldestDate] & "|" [CRLOldestDate])

And it should return the earliest one.

 

[Vergy39]

Thanks Bob for the quick replay. I think this will work, however, I need to know how to place it into the control source of the text box that I created on the report. I tried what you provided, but I got errors. How would you enter this into the control source?

Thanks

 

[boblarson]

Thanks Bob for the quick replay. I think this will work, however, I need to know how to place it into the control source of the text box that I created on the report. I tried what you provided, but I got errors. How would you enter this into the control source?

Thanks

1. Make sure that there are NO text boxes named the same as the field names.

2. Then use this in the control source:

=ReturnEarliestDate([TruePresOldestDate] & "|" & [BBBOldestDate] & "|" & [VerbalOldestDate] & "|" [CRLOldestDate])

As long as those are the FIELD names and they are in the query underlying the report.

But I would put it into the query instead and then you just need to bind the text box to the new field name.

 

[vbaInet]

Maybe a ParamArray would have sufficed to avoid Split() and "|".

Function ReturnEarliestDate(ParamArray strInput() As Variant) As Date
     dim i as Byte

     for i = lbound(strInput) to ubound(strInput)
...

 

[Vergy39]

Thanks Bob, still no luck. I am getting an error that states "The expression you entered contains invalid syntax. You may have entered an operand without an operator.

I also tried adding it to the query, but my query already sorts by oldest date by associate, so I could not do that. I do have a Query that gives me the oldest date of all issues, but I am having problems getting that in the report. The text box I added to the report, I have trie to put the query and filed in the control source, but when I pull the report, it asks for the oldest date. Sorry to be such a bother.

Thanks
David V.

 

[vbaInet]

Did you prefix the function name with this = ?

 

[boblarson]

Works fine for me so it has to be something you are either doing or not doing. So, if the last suggestion by vbaInet doesn't help, then perhaps if you post a copy of your database (with bogus data of course) we can take a look.

 

[boblarson]

Maybe a ParamArray would have sufficed to avoid Split() and "|".

Function ReturnEarliestDate(ParamArray strInput() As Variant) As Date
     dim i as Byte
 
     for i = lbound(strInput) to ubound(strInput)
...

Good point. I had forgotten about those. So revised code would be:

Function ReturnEarliestDate(ParamArray strInput() As Variant) As Date

   Dim i As Integer
   Dim dteHold As Date
 
On Error GoTo Errors
    
    dteHold = strInput(i)
   
   For i = LBound(strInput) To UBound(strInput)
      If i > 0 Then
         If CDate(strInput(i)) < dteHold Then
            dteHold = CDate(strInput(i))
         End If
         
        End If
   Next i
    
ReturnEarliestDate = dteHold
 

ExitHere:
     Exit Function
Errors:
           
            MsgBox "Error " & Err.Number & " -   (" & Err.Description & ") in procedure ReturnEarliestDate of Module Module20", , CurrentDb.Properties("AppTitle")
            Resume ExitHere
            Resume
End Function

And then the call would simply be:

In a query -

NewFieldName:ReturnEarliestDate([FieldName], [FieldName2], [FieldName3], [FieldName4])

or in a control source:

=ReturnEarlisetDate([FieldName], [Field2Name], [Field3Name], [Field4Name])


Also, did you put the code into a STANDARD MODULE and NOT in a form or report module? And the module name can't be the same as the function name.

 

[Vergy39]

Thanks to the both of you. I ended up creating a subreport and added it to the daily report. This will suffice for now, but I do want to make it more simplified. I will try the other suggestions tomorrow. I did put an = sign in front of the string that I added to the control source. I also looked at your web page and signed up for your news letter. Hope this will help me in the future.

Thanks
David V.