Dynamic control source for textbox

[templeowls]

I have a crosstab query with column headers stylised as "ABC_2024", "ABC_2023", etc.

In a report linked to the query, I have a textbox that I want the control source to always be linked to the field "ABC_(previous year)". So for this year, it'd be "ABC_2023". But once it turns 1/1/2025, the control source automatically links to "ABC_2024" instead of me having to manually change it. Is there a way to achieve this?

I (stupidity) tried putting this in the control source:

="ABC_" & Year(Date())

But this just displays "ARO_2023" in the control source rather than the value from the query. Maybe it's not possible but I thought I'd check. Thanks!

[Gicu]

Do it in the Open event of the report:

Private Sub Report_Open(Cancel As Integer)
Me.txtPrevYear.ControlSource = "ABC_" & Year(Date())-1
End Sub

Cheers,

[templeowls]

Thank you! That worked!