Set ControlSource of Report TextBox to String

[colintis]

Also in some additional function I want to add to the report that it generate. The title of the report includes a date range according to the date selected from the form. The additional function I want to add is to detect whether the From and To dates are the same, then the title of the report will includes a single date instead of 2 dates.

E.g.
For a single date will be: Report of Area (2010/05/28).
And for date range will be: Report of Area (2010/01/01 - 2010/05/28).

I have the following code in the report_open

    If [Forms].[rptArea].[txtFromDateA] = [Forms].[rptTrainingHrs].[txtToDateA] Then
        txtTitle.ControlSource = "Total Training Hours in Area (" & [Forms].[rptArea].[txtFromDateA] & ")"
    Else
        txtTitle.ControlSource = "Total Training Hours in Area (" & [Forms].[rptArea].[txtFromDateA] & " - " & [Forms].[rptArea].[txtToDateA] & ")"
    End If

And when execute it shows error 438 and "Object doesn't support this property or method"

** Edit **
This question was split from Report generation with filter in form.

[NeoPa]

The .ControlSource property in a report's TextBox is certainly the right thing to use, but you need to consider first exactly what you expect to see in there. Currently you are presenting a value of :

Total Training Hours in Area (28/5/2010)

or something quite similar.

This is treated in the report as the name of a field within the .RecordSource (as there are no quotes - to make it a string, and no "=" to indicate a literal value).

Try putting the same effect in the report manually. You will notice that it expects, and displays, the data as :

="Total Training Hours in Area (28/5/2010)"

For this you need :

    Dim strTitle As String

    With Forms.rptArea
        strTitle = IIf(.txtFromDateA = .txtToDateA, "", " - " & .txtToDateA & ")"
        txtTitle.ControlSource = _
            Replace("=""Total Training Hours in Area (" & .txtFromDateA & "%T)""", _
                    "%T", strtitle)
    End With

Notice the format of the string which is shown in line #6. If you use MsgBox() to display the value you'll see it comes out as :

="blah blah blah"

Exactly as required.

PS. When I get a second I'll split this new question into its own thread, as multiple questions in the same thread can make life confusing, not only for the main participants, but also the many that follow afterwards. Please ensure you avoid this in future.

[colintis]

The .ControlSource property in a report's TextBox is certainly the right thing to use, but you need to consider first exactly what you expect to see in there. Currently you are presenting a value of :

Total Training Hours in Area (28/5/2010)

or something quite similar.

This is treated in the report as the name of a field within the .RecordSource (as there are no quotes - to make it a string, and no "=" to indicate a literal value).

Try putting the same effect in the report manually. You will notice that it expects, and displays, the data as :

="Total Training Hours in Area (28/5/2010)"

For this you need :

    Dim strTitle As String

    With Forms.rptArea
        strTitle = IIf(.txtFromDateA = .txtToDateA, "", " - " & .txtToDateA & ")"
        txtTitle.ControlSource = _
            Replace("=""Total Training Hours in Area (" & .txtFromDateA & "%T)""", _
                    "%T", strtitle)
    End With

Notice the format of the string which is shown in line #6. If you use MsgBox() to display the value you'll see it comes out as :

="blah blah blah"

Exactly as required.

PS. When I get a second I'll split this new question into its own thread, as multiple questions in the same thread can make life confusing, not only for the main participants, but also the many that follow afterwards. Please ensure you avoid this in future.

Sorry for that NeoPa, I'll put a link or something to make the refer if there's related in the 2 question from next time.

And thank you, the code you suggested is shorter and simpler then using IF statement.

[NeoPa]

I'm pleased it helped :)

BTW it's nice to see you answering some threads too. It's always good to see members taking their time to help too. Nice one.

[colintis]

I'm pleased it helped :)

BTW it's nice to see you answering some threads too. It's always good to see members taking their time to help too. Nice one.

Thanks for that, I'm glad my learnings can give a hand as well, rather than just asking questions all time.

I'll stop the off topic in here. :-)