very sorry, my last suggestion had at least 2 problems. 1: I was thinking
pubMonth was in a General module and 2: the syntax for working with
ControlSource was wrong.
If pubMonth is a module-level public variable in the code module attached to
frmCriteria (and frmCriteria is open), you could do this in the Report_Open
event (and this has been tested):
Me.txtMonth.ControlSource = "=' " & Forms("frmCriteria").pubMonth & "
Status Report' "
Note: spaces between ' and " between ' and " added for clarity.
Afaik, Controlsource can't resolve a direct reference to a variable that
exists only in code, so you either need to either:
1) assign the controlsource value via code at runtime (rather than design
view) like I have above,
or
2) create a simple public function in a General Module to return the value
of pubMonth. The function can be entered into Controlsource at design-time
where the variable couldn't be:
= GetPubMonth() & " Status Report"
Sample function (in a general code module, not one attached to a form or
report)
Public Function GetPubMonth()as String
On Error Resume Next 'return empty string without error if Form isn't
open
GetPubMonth = Forms("frmCriteria").pubMonth
End Function
HTH,
George
Scott said:
Again thanks for the suggestion, but it still asks for a 'parameter value'
for the user to input.................
In frmCriteria, I define the variable pubMonth = "January", and call
rptStatus. I set up an event so that when rptStatus opens it defines
Month.controlstatus = pubmonth & "Status report". A parameter opens with
the
heading of "January" so I enter something and that appears in the report
followed by "Status Report"........This is exactly the same situation when
I
put [] around "pubmonth"
George Nicholson said:
If you have a variable called pubMonth, you want:
Month.controlsource = pubMonth & " Status Report"
not
Month.controlsource = [pubMonth] & " Status Report"
The brackets tell Access that it should be looking for a *field* named
PubMonth, and triggers a prompt when it isn't found.
--
HTH,
George
Zac,
First let me thank you for your quick response. The problem is that my
ControlSource value changes depending on criteria selected by the user.
More
specifically, I set up a text field in the report named "Month" and
attempted
to use a public variable named "pubMonth" so that my ControlSource for
Month
is = [pubMonth] & "Status Report". This, however, calls on the user to
re-enter the Month as it does not recognize the value already store in
the
public variable. This is a redundant entry that I am trying to avoid
and
since the selection by the user may require multiple months (Quarters
or
even
Annual) the public variable "pubMonth" could also have values equal to
"Quarter Ending March, 2008" or "Annual" or "Fiscal Year". I have an
entire
table of relationships between what the user selects, what data is
displayed
and what I want the report header to display....................I don't
want
the user defining the report header. Does this better explain my
dilema.
I've tried resetting the text field by setting up an event when the
report
is
activated: Month.controlsource = [pubMonth] & " Status Report", but
this
also
forces the user to input a value for "pubMonth"
:
Scott ---
You can add a Text Area field to the report title, bring up the
Properties of the field, click Data tab, and enter the info you want
to display in the Control Source field. Assuming a data field called
"Date", you could do the following:
Show the date ("Items Dated 1/15/08")
="Items Dated " & [date]
**note, if you have multiple date values this will only display the
first record
Show the range of dates in your report ("Items from 1/1/08 - 5/1/08")
="Items from " & Min([date]) & " - " & Max([date])
Additionally, you can use any of the built-in date functions to format
the date as you like. For instance entering =MonthName(Month([date]))
& ", " & Year([date]) will yeild "January, 2008" if the value for
[date] is from 1/1/08 to 1/31/08.
Hope that helps!
Zac
