Refer to Query Field in Unbound Report 1

[wonderwhy]

I have two questions:

1) I have an unbound report with 6 subreports on it. Each subreport runs off of parameter queries that ask for the user to enter a year. They will be entering the same year for each query but the year doesn't always come from the same field in the original tables. Aside from entering the year six times is there a way to allow them to enter the year once and use it for each query in each report?

2) This relates to the same report above. I would like to use the "Year" that chosen when previewing the above report w/subreports. Each subreport is based on a progression of queries (at least one regular query and then a crosstab query) and the "Year" field is not viewed in the crosstab or the subreport. How can I refer to the "Year" value on the report header of my unbound report.

Yikes! Thank you for any help.

Julia

 

[cghoga]

Regarding question 1....

is the Year stored in a field itself, or are you needing to query a full date field? for example, is the field you're querying of format 01/01/2003 or 2003? if it's a field that just represents a year and nothing else what I have added below should work. (if not, you probably need to parse out the year before querying the data. if the latter, and you need help, please let me know.)

are you clicking a command button to kickoff the reports?
well, on the form where you kickoff the reports, create an unbound text field called Year. Refer to this field in the parameters of each query within the field where you're querying for the Year.

for example, within your queries, in the query criteria of your "Year" field, include something like this:

Forms!YourFormName!Year.value

During runtime, keep the form where the field resides open as each query when it runs will be referencing this field.


Regarding question 2....

Create an unbound text box in your report header
In the control source, reference the same location of the Year field above.
In the control source enter...

=Forms!YourFormName!Year.value

Remember, for this to work, the form you're referencing must be open, otherwise you will get bad results.

I hope this helps you.

 

[wonderwhy]

Thank you, cghoga. I have already formatted the date as just a year in my underlying queries. Thank you for the offer of help with that.

I am quite sure your solutions will work. I will implement them and let you know if I have further questions.

Thank you for the prompt response!

Julia

 

[wonderwhy]

cghoga,

I have implemented your solutions above and they work great.

Thank you so much!

Julia