Populate unbound date field on report based on grouping

[Waubain]

I track all projects in our department and their time line. A project can be in 1 of 4 stages: Idea, Development, On Going, and Complete. These are in an option group stored in StatusDescription. Associated with each option is a separate date field named: IdeaDate, DevelopmentDate, etc. In the report, bound to a query, I group projects based on StatusDescription. In the detail section there is an unbound textbox named RefDate. I am trying to populate RefDate based on StatusDescription. Here is what I tried. The row source for RefDate is blank. Any help would be appreciated.

Private Sub Detail_Format(Cancel As Integer)

    If StatusDescription = "Idea" Then
        IdeaDate = RefDate

    ElseIf StatusDescription = "Development" Then
        DevelopmentDate = RefDate
        
    ElseIf StatusDescription = "On Going" Then
        OnGoingDate = RefDate

    ElseIf StatusDescription = "Complete" Then
        CompleteDate = RefDate

    End If

End Sub

[boblarson]

The unbound reference would need to refer to a control on a FORM not the report.

[Waubain]

I am not sure I understand. The unbound textbox RefDate only resides on the report and is not required anywhere else. The dates come from the four date fields. I am trying to figure out how to have only 1 field on the report, but 4 possible choice based on the Grouping header [StatusDescription]. If the grouping header is "Idea" , then I want RefDate to be populated from the IdeaDate field in the query related to that project, if grouping header is Development then populated from DevelopmentDate, etc.

Thanks for your help.

[boblarson]

How does the unbound text box get a value? If you are trying to set something else to its value (which is what the code does) then it must have a value when the report opens. So, where does it come from? Or do you have your formula accidentally reversed where you are trying to say something like:

Me.RefDate = OnGoingDate

[Waubain]

I probably have Me.RefDate = OnGoingDate backwards but this is what I am trying to do. I reversed the order in the code, but the RefDate field is still blank. The code sits in the detail section. Does it need to appear when the form open. Most of the examples that were close to what I was trying to do used the Detail_Format.

I tried to paste the code in the query and then reference the control source to that field, but I kept getting a expression error and examples on Google don't seem to help.

[Waubain]

I was able to get this to work by nesting 4 IIf statements in the query and then referencing that as to my RefDate field. From what I read, that may not be the best way, but its works for now.