Text box showing error on report

[john]

My report is based on a query with user input [Enter year]. In my report I
have a text box with the following code: =[Enter year]. This works when the
query outcome is 1 or more records. But when the query output is zero
records this field shows #Error while I would like this text box to still
show the year the user entered. The user input doesn't seem to be available
when there is no query outcome.
Anyone an idea to make this possible?
thanks,
john

[fredg]

On Sun, 24 Sep 2006 07:34:58 +0200, john wrote:

My report is based on a query with user input [Enter year]. In my report I
have a text box with the following code: =[Enter year]. This works when the
query outcome is 1 or more records. But when the query output is zero
records this field shows #Error while I would like this text box to still
show the year the user entered. The user input doesn't seem to be available
when there is no query outcome.
Anyone an idea to make this possible?
thanks,
john

Use an unbound form instead of the query [enter year] prompt, to enter
the query parameter. As long as the form is open, the report, even
with no data, will read the form control.
Here's how.

Create an unbound form.

Also add an unbound text control.
Name this control 'txtYear'.

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query field write:
forms!Paramform !txtYear

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

As control source of that control in the report where you wish to
display the entered year parameter, write:
= forms!Paramform !txtYear

When ready to run the report, open the report.
The form will open and wait the entry of the wanted year.
Click the command button and then report will run.
When the report closes, it will close the form.

The year entered in the form control will display in the report, data
or no data.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[john]

"fredg" <fg******@examp le.invalidschre ef in bericht
news:1l******** *************** ******@40tude.n et...

On Sun, 24 Sep 2006 07:34:58 +0200, john wrote:

>My report is based on a query with user input [Enter year]. In my report
I
have a text box with the following code: =[Enter year]. This works when
the
query outcome is 1 or more records. But when the query output is zero
records this field shows #Error while I would like this text box to still
show the year the user entered. The user input doesn't seem to be
available
when there is no query outcome.
Anyone an idea to make this possible?
thanks,
john

Use an unbound form instead of the query [enter year] prompt, to enter
the query parameter. As long as the form is open, the report, even
with no data, will read the form control.
Here's how.

Create an unbound form.

Also add an unbound text control.
Name this control 'txtYear'.

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query field write:
forms!Paramform !txtYear

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

As control source of that control in the report where you wish to
display the entered year parameter, write:
= forms!Paramform !txtYear

When ready to run the report, open the report.
The form will open and wait the entry of the wanted year.
Click the command button and then report will run.
When the report closes, it will close the form.

The year entered in the form control will display in the report, data
or no data.

Thanks! That helps.
john