Determining Latest of 5 Date Fields

[PaulaJ]

I'm stuck. I have five separate Date Fields in my record. For a report I'm doing, I only have room for one Date. I need to be able to determine which of these five Dates is the latest. This is the pseudocode for what I need to do but can't get a handle on the Access capability.

If Date5 is not Null, make printed date Date5 else
If Date4 is not Null, make printed date Date4 else
If Date3 is not Null, make printed date Date3 else
If Date2 is not Null, make printed date Date2 else
If printed date is still Null, make printed date Date1.

Any pointers would be appreciated.

 

[hneal98]

You don't say if you are trying to do this in a query or if you are doing this in VBA.

 

[PaulaJ]

Thanks for the quick response. I need to display this date on the detail line of the report so whatever will work. I've done quite a bit of VBA but just can't get the syntax right. I also tried a query, but appear to have too many or values to get it to work. Any suggestions or directions are appreciated.

 

[CosmoKramer]

Try something like this as the control source of an unbound text box:

=IIf([Date5] Is Not Null,[Date5],IIf([Date4] Is Not Null,[Date4],IIf([Date3] Is Not Null,[Date3],IIf([Date2] Is Not Null,[Date2],[Date1]))))

Hoc nomen meum verum non est.

 

[MichaelRed]

see thread thread705-590307 and the post including basMaxVal.

Perhaps not functionally different, but a bit easier to use / type.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CosmoKramer]

Michael,

At first I was thinking along those lines too, but PaulaJ's pseudocode seems to say she wants the last numbered date field that is populated, not necessarily the min or max value.......

Hoc nomen meum verum non est.

 

[SgtJarrow]

PaulaJ....Here's my take:

Assumption: you have five fields, each possibly with a date, available in the query which is the recordsource of your report or as hidden fields in your report.

Solution: Start a new module and paste the following code in:

' ************** Start Code ************

Public Function GetDate(Optional dteDate1 As Date = #12/31/1899#, Optional dteDate2 As Date = #12/31/1899#, Optional dteDate3 As Date = #12/31/1899#, Optional dteDate4 As Date = #12/31/1899#, Optional dteDate5 As Date = #12/31/1899#) As Date

If dteDate5 <> #12/31/1899# Then
GetDate = dteDate5
Exit Function
End If
If dteDate4 <> #12/31/1899# Then
GetDate = dteDate4
Exit Function
End If
If dteDate3 <> #12/31/1899# Then
GetDate = dteDate3
Exit Function
End If
If dteDate2 <> #12/31/1899# Then
GetDate = dteDate2
Exit Function
End If
GetDate = dteDate1

End Function

' ************** End Code **************

If using a query, add a new field at the end and use:

ReportDate: =GetDate([Field1],[Field2],[Field3],[Field4],[Field5])

in the field line and then set the controlsource of the field in the report to:

[ReportDate]

If in the report directly, it is basically the same....using the OnFormat event of the section in question, put:

Me![reportfieldnametodisplaydate] = GetDate(Me![Field1], Me![Field2], Me![Field3], Me![Field4], Me![Field5])

This basically takes the input, and checks it. If there is no date for any of the fields, it sets the date to the bogus date of 12/31/1899. It then walks backwards and looks for the first date not equal to 12/31/1899....and retuns that date...

Any Questions???

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[PaulaJ]

Thank everyone for their input. This forum has saved me on many occasions. I actually put the code CosmoKramer suggested in a text box and it works perfectly. I've saved the rest of the suggestions for exploration later. I'm pretty much the only Access developer here and you never know what you'll need that you don't have. Thank everyone again.

 

[MichaelRed]

CosmoKramer & Robert. As In read it PaulaJ is simply looking for the 'last' (ordinal?) date field which is not null.

I am (sort of) assuming that the dates are filled in in the order (of the fields) and are ascending. If such is true, the function (basMaxValue) checks for the null and does not consider it and the order (first to last) would have the correct date returned. I (admmittedly) took advantage of a not necessarily obvious feature of the routine which was designed (and nammed) with a slightly diffferent purpose, however it is just a convenience to save the generation of yet another procedure and / or the tedium of typing the nested IIF along with the counting of parens to make sure they are balanced.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places