Modifying text boxes in a report

[SeanBanks]

Hello,

I'm editing a report that was created by someone else. The report has about
100 different text boxes. An example of the code in a text box is:

=DCount("[ms_id]","Patient_Status","[gender]=1 and [site]=1 and [refuse]=2")

So there are a bunch of columns (i.e. when site = 2, 3, 4, or 5) and 16 rows
(when gender = 2, or refuse = 1, age = <65, etc.)

I need to add another variable ( [year]=2008 ) to all of these text boxes.
Then create another report for 2007 and 2006.

How can I do this without going into each text box and changing the criteria
individually?

Thank you in advance.

 

[JBinQLD]

G'day,

Assuming the controls are similarly named (Eg., txtH1, txtH2, txtH3, etc) create an on open event on the report and give this a go: (Untested but adapted from something I use to make report captions dynamic.)

dim ctl as control
dim intcounter as integer
dim strCS as string

'assume 20 controls
For intCounter = 0 To 20
    Set ctl = Me("txtH" & intCounter)
    strCS=ctl.ControlSource
    'trim ") from string
    strCS=mid(strCS,len(strCS)-2)
    'add new part of string
    strCS=strCS & " AND [year]=2008 "")"
    ctl.ControlSource = strCS

next intCounter

Good luck,

JB