Column Comparison

[FlyGuyTray]

I have a table. In this table is 3 different date columns. Is there a way that access can look at the dates in those columns and output the earliest date of the 3 into another column within the table?

 

[boblarson]

Well, it is likely that you shouldn't have 3 date fields like that but since you do, you can use my function here:
http://www.btabdevelopment.com/ts/earliestdate

paste it into a new STANDARD module (not form, report or class module) and name the module something other than the function name.

Then you just pass the information in a query like this:

EDIT (I fixed this):
Earliest:ReturnEarliestDate([Field1NameHere] & "," & [Field2NameHere] & "," & [FieldName3Here], ",")

The quotes have to be there just like I show and the delimiter used, in this case a comma, has to be passed as well.

 

[FlyGuyTray]

Thanks so much sir!

However I get an error at this line:

dteHold = varSplit(i)

 

[boblarson]

Thanks so much sir!

However I get an error at this line:

dteHold = varSplit(i)

Did you see that I edited my call to the function in the post? I had accidentally posted it incorrectly and so I have fixed it. I think that is the problem - that you used my original example. I forgot (it's been a while since I did do that one and I don't use it) that you have to concatenate in the delimiters instead of passing one long string.

 

[FlyGuyTray]

Ahhhhhhh i see!

 

[FlyGuyTray]

Question: if there is a null in one of the 3 columns will the it produce in an error?

 

[boblarson]

I guess I missed that. I'll fix my function and you should too. So add the parts in red to the part shown:

    For i = LBound(varSplit) To UBound(varSplit)
        If i > 0 Then
            [COLOR=red][B]If varSplit(i) <> vbNullString Then[/B][/COLOR]
                If CDate(varSplit(i)) < dteHold Then
                    dteHold = CDate(varSplit(i))
                End If
            [B][COLOR=red]End If[/COLOR][/B]
        End If
    Next i

 

[FlyGuyTray]

Still throwing errors..

 

[boblarson]

Still throwing errors..

Post the exact code you are using and post the code which calls the function or, if in a query, post the SQL of that query.

And what is the exact error?

 

[FlyGuyTray]

Function ReturnEarliestDate(strInput As String, strDelimiter As String) As Date



Dim varSplit As Variant
Dim i As Integer
Dim dteHold As Date

varSplit = Split(strInput, strDelimiter, , vbTextCompare)
dteHold = varSplit(i)
For i = LBound(varSplit) To UBound(varSplit)
If i > 0 Then
If varSplit(i) <> "" Then
If CDate(varSplit(i)) < dteHold Then
dteHold = CDate(varSplit(i))
End If
End If
End If
Next i

ReturnEarliestDate = dteHold

End Function



Earliest: ReturnEarliestDate([1INSTDATE] & "," & [2INSTDATE] & "," & [COMMENTS],",")

 

[boblarson]

Is Comments really a date field? Is that really just a date/time datatype?

 

[FlyGuyTray]

Comments wasnt at first a date field but i made it a date field.. It had text and a date in the field. I removed the text to leave the date and changed the type to date.. Basically the 3 date fields are installation dates for a piece of equipment. unfortunately because of bad record keeping we ended up with 3 dates for a single piece of equipment. We want to know the earliest date out of those 3 because thats the best estimate as to when cable between the 2 pieces of equipment was installed..

 

[boblarson]

It should work if it is a date field. If it was not, then it would not work.