Generate Date Field to based on Group value

[Guest_imported]

What is the easiest way to get a date field into my report if it is dependent on the group value.
What I mean is I have a report which groups research projects according to the stage they are at: Confirmed, Fieldwork Commenced, Debriefed. Each one of these stages has a different date, but each date is in the projects table. I have a report which sorts by Stage reached. For each section I need to include the relevant milestone date, so for projects confirmed I need to include the confirmed date. For projects with fieldwork begin/finished I need to put the fieldwork date, but not the confirmed date; for those projects which have reached debrief I need to put the debrief date but not the other two.
I have tried doing it with VB using an If Then Else but can't work out where to attach it.
Would a macro be easier?

 

[MichaelRed]

In your query, add a calculated field ("MileStoneDate" - or your choice of name). Set this to the VBA procedure name of yout IF statement, too (obviously?) include the milestone ID and the dates. The query would look something like:

MilsStoneDate: basSelectDate([MileStoneA], [MileStoneB], [MileStoneC], [MileStoneD], [DateA], [DateB], [DateC], [DateD])

The arguments would be the four Milestone completion flage (Booleans) and the four dates for the completion. The routine would check = from last to first - the flage. The first one to be found would determine the date to be returned.

This CAN all be done directly within the query, with the same arguments and a set of nested IIF statements, but it gets a bit difficult to read and you need to be EXTREMELY careful about the opunctuation (Commas and Parens).

MilestoneDate: IIF (MilestoneD, DateD, IIF(MIleStoneC, DateC, IIF (MileStoneB, DateB, DateA)))

This ASSUMES that the project is NOT listed unless at least MileStone A has been reached. Otherwise, an additional IIF clause needs to be included.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over