Show Date nearest to today's date

[gstory]

Trying to work on a formula to show the last scheduled event that has a date nearest to today's date. All other data would not come up in the report.

If my database has the following entries for case C-SV-00011:

Case ID Caption Scheduled Event Result Date
C-SV-00011 Paul Vs Smith 44409 11/05/2002
C-SV-00011 Paul Vs Smith 44410 11/09/2002
C-SV-00011 Paul Vs Smith 21412 11/25/2002
C-SV-00011 Paul Vs Smith 57291 12/06/2002

Then I need the report to show me the below line as it’s the newest result date to today’s date in the database.
C-SV-00011 Paul Vs Smith 57291 12/06/2002

Anyone have any ideas?

Gerald Story
MIS

 

[DocFKNx]

Doesn't this get you started towards that goal? (Basic syntax):

Formula = CurrentDate - {TABLENAME.DATEFIELD}

You can group on this / sort on it etc. Smallest values are the dates closest to the target. Negative values will be future dates, etc.

X

 

[GJParker]

If there aren't any future dates in your table you could add a group mselection formula of

{TABLENAME.DATEFIELD} = Maximum({TABLENAME.DATEFIELD})

This will filter your records to only show the highest date

Hope this helps. Gary Parker
Systems Support Analyst

 

[gstory]

Thanks to the both of you. I will test these out today.

Thanks again

Gerald