Using global variables in reports

Topic

New Reply

In my database I have defined a global variable ‘gstrSubsYear’. I can check that it is initialising correctly by putting a text box on a form and setting it to gstrSubsYear in the form’s OnOpen event.

I am trying to use it on a report by having a text box’s control source property set to ‘=”Year to April ” & gstrSubsYear’. Access pops up a parameter box asking for gstrSubsYear and has converted & gstrSubsYear into [gstrSubsYear].

I thought I knew about concatenation of strings but have never come across this behaviour before. How do I get the field to recognise the global variable?

Regards

David

Reply | Quote

Replies

You can use global variables in VBA code but not in queries and in expressions such as the control source of a text box.

To get around this, create a function in a standard module that returns the value of the variable:

Code:

Public Function GetSubsYear() As String
  GetSubsYear = gstrSubsYear
End Function

You can use it like this:

=”Year to April ” & GetSubsYear()

Reply | Quote

Many thanks Hans. I hadn’t come across that limitation before.

Regards
David

Reply | Quote