Need help with something that will look a multiple field and based on the field that meets the criteria, us the value in that field (date) to populate a new field that I can use on a report.
if [startdate1] is Between Date() And Date() +60, then use [startdate1],
if [startdate2] is Between Date() And Date() +60, then use [startdate2],
if [startdate3] is Between Date() And Date() +60, then use [startdate3],
if [startdate4] is Between Date() And Date() +60, then use [startdate4]
All of this information is entered into one table (Lease document). I have 5 different date fields that do not overlap. I want to be able to look at all of the dates and use the date field that is from today to 60 days in the future. I can select the records that meet this criteria.
There is also 5 different currency fields that are associated each one of those dates fields. In a query (for a report), I need to display the date field (I was thinking in a new field) that meets the criteria and associated currency so I can due some calculations on those numbers.
I also looked at trying to create different table where the associated date and currency fields are entered, but I need to be able to entered multiple records from one Form that also has information which belongs to another Table.
ANY IDEAS...PLEASE!