Sounds like dates that should be stored in related records rather than fields.
But do you want the name of the field or the date stored in it?
And is it possible that one or more of the dates in these fields might be a date in the past?
Thanks for the reply.
I perhaps should explain it in a little more detail.
I am building a property database that includes, amongst other things, 3 fields:
Next Rent Review
I then need a field to show me when the next event is and another field showing how far away that event is.
You could use a table of "events" where a text field identifies the type of event and a date field records its date. A sorted relationship could then be set up so that the nearest pending date is the first related record. The Subtracting that date from Get ( CurrentDate ) then computes "how far away it is" by computing the number of days between the two dates.
MainTable::cToday > Events::EventDate
cToday would be an unstored calculation field: Get ( CurrentDate ) with Date specified as the result type.
This relationship can be sorted in ascending order by EventDate and then a reference to fields from Events when placed on the MainTable layout will show data, such as the event name and date for the next upcoming event.
I'm trying to do this, too. I have two tables: Assets, Events.
- Each event has a Start Date.
- Each Asset can be included "on tour" via a checkbox and associated with an event in a separate field.
I'd like the nearest event to automatically be associated with an asset when the "Include on tour" option is selected.
I tried following your directions above, but I feel like I'm missing something. I created Assets::cToday (calculation: Get (CurrentDate); result: Date) and created the sorted relationship as described. I'm not sure what to do with this: "Then Subtracting that date from Get ( CurrentDate ) then computes "how far away it is" by computing the number of days between the two dates." Is a calculation in another field? How does this connect a specific Event to the chosen Asset?