Query..Module..Macro....HELP!!!

[RIRinkRat]

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!

[pbaldy]

Cross posted, but I'll give you the same answer here. The dates and currencies should be in a related table.