Displaying a prompted variable in a report 2

[MrDeveloper]

Hi,
I have a query-based report that prompts a user to enter in a start date that falls between two dates. Works as intended with the records between those dates being displayed.

What the client now wants is the heading of the report to show the dates entered in at the prompts when the report starts.

E.g.
Prompt: (Please enter in) Start Date 1: 01/01/02
Prompt: (Please enter in) Start Date 2: 01/01/04

-------------REPORT----------------------
Management Report
Date Run: 16/01/05
Title: All records with a Start Date between 01/01/02 and 01/01/04
----Record1---
----Record2---
----Record3--- etc.
-----------------------------------------

Can this be done, and if so do changes need to be made to the original query or the report?

Working query snippet currently in use:

SELECT Matches.ID, Matches.Activity, Matches.Start_Date, Matches.End_Date
FROM Matches
WHERE ((Matches.Start_Date) Between [Start date 1] And [Start date 2])
ORDER BY etc..

Any pointers much appreciated,

 

[THWatson]

MrDeveloper
In your report header, you can put an unbound text box with its conrol source being something such as...
="This report contains data from " & [Start date 1] & " to " & [Start date 2]

Be very careful to enter the prompts so that they are exactly as they exist in your query, or you will get prompted twice. So if you get prompted twice it's a signal that you haven't entered the parameters exactly as they are in your query.

Another approach would be to call the dates from an unbound form that would have two text boxes in which the dates are entered. Let's say the form could be called frmDateSelector, and one text box would be txtStartDate and the other text box would be txtEndDate. Also on the form would be a command button to run the report.

Then in your date column in the query, put the following as criteria...
Between Forms!frmDateSector!txtStartDate And Forms!frmDateSelector!txtEndDate

Then make your expression in the report header...
="This report contains data from " & Forms!frmDateSelector!txtStartDate & " to " & Forms!frmDateSelector!txtEndDate

Hope that helps.

Tom

 

[MrDeveloper]

Tom,
Many thanks for your advice. The first suggestion did not work (resulted in #error appearing in the form?) although I might play with this further as this would be the ideal way forward (i.e. keeping the prompt pop-ups for the user).

The second suggestion was a helpful alternative if Nos.1 did not work - will use this if all else fails!

Do let me know if you have had #error appear even if the variables match word-for-word with the prompt names, and have an idea why this might be.

Otherwise, thank you again for your help.

MrD.

 

[THWatson]

MrD.
Normally, a #error means that you didn't start with an "unbound" text box in your report header, but rather used a text box bound to a field and then tried to reconstruct it to include the parameters.

What type of query are you basing the report on? If it's a crosstab query, you may have to declare the parameters in the the Query>Parameters tab in design view.

Post back with more specifics if you still have trouble.

Although I get your point about having the pop-up prompts for the user, there are some drawbacks to that approach that are not existent if you drive the report from a form. However, obviously, it's your call.

Tom

 

[MrDeveloper]

Tom,
Works a treat! Thanks! For those reading wanting to do similar:

I dropped a new text-box on the form but made sure it was unbound then literally typed in the variable name (=[Start date 1]) prompted for, in the control source section of the object properties, no further work required.

I agree about pop-ups and personally I would prefer things to be form driven, but afraid to say its a case of what the users want

Thanks again,
MrD.