Reduce Multiple Query Parameter Selections

[PeanutB7]

I have a database running three parameter queries requiring dialog boxes for each. The user is asked to "Select a start date in the format xx/xx/xxxx" and the same for an end date. The selection for all three queries must be the same for proper reporting. For reasons of accuracy and convenience is there a way to inform all three parameter queries with one set of dialog boxes.

Thank you in advance for the help.

JB

 

[PHV]

Yes, simply use a parameter form with a button for launching the queries when the dates are properly populated by the user.
In the queries:
PARAMETERS [Forms]![your parameter form]![start date] DateTime, [Forms]![your parameter form]![end date] DateTime;
SELECT blah blah
FROM blah blah
WHERE ([some date field] Between [Forms]![your parameter form]![start date] And [Forms]![your parameter form]![end date])
AND blah blah

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THWatson]

PeanutB7
I'm not sure how you are calling the queries, but would the following approach work for you?

1. Create an unbound form. Let's call it frmReportSelect
2. On the form create two unbound text boxes. Let's call one txtStartDate, and the other txtEndDate.
3. In each of your queries, in the criteria for the Date column, put...
Between Forms!frmReportSelect!txtStartDate And Forms!frmReportSelect!txtEndDate

The other possibility might be to join your 3 queries together and then just enter the criteria once.

Tom

 

[PeanutB7]

Hi folks,

Thanks for the two suggestions. I have created a form called frmReportSelect and replaced the criteria with Tom's recommendation. Regretfully I am getting two small parameter dialog boxes again with the heading '

Forms!frmReportSelect!txtStartDate' and
Forms!frmReportSelect!txtEndDate'

I tried PHV's suggestion as well and got the same exact result. The query does not seem to recognize my form exists. Please send a message as to what I am doing wrong and again thank you both sooooooooooooo much!


JB

 

[PHV]

I tried PHV's suggestion as well
Could you please post the SQL code ?
BTW, frmReportSelect must be an open mainform at the time the query is launched.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PeanutB7]

Sorry I neglected to post the code. Thanks for your help. This will be a significant improvement to our database.


PARAMETERS [Forms]![frmReportSelect]![txtStartDate] DateTime, [Forms]![frmReportSelect]![txtEndDate] DateTime;

SELECT tbl_Organ_ProjectInformation_Days5.ID, tbl_Organ_ProjectInformation_Days5.[Number of Initiatives], (Count(*)-1) AS CountofDays, '..........etc.'

FROM tbl_Organ_ProjectInformation_Days5, tblDates

WHERE (((tblDates.TheDate) Between [Start Date] And [End Date] And (tblDates.TheDate) Between Forms!frmReportSelect!txtStartDate And Forms!frmReportSelect!txtEndDate))
GROUP BY tbl_Organ_ProjectInformation_Days5.ID, tb '........etc.'tbl_Organ_ProjectInformation_Days5.CIOed;



Thank you once again for the help.

JB

 

[PHV]

WHERE (((tblDates.TheDate) Between [Start Date] And [End Date] And (tblDates.TheDate) Between [Forms]![frmReportSelect]![txtStartDate] And [Forms]![frmReportSelect]![txtEndDate]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PeanutB7]

Thanks for the fast response and I hate being a pain in your keyboard but I got the same result; two small parameter dialog boxes. It will not recognize the form and I have verified the form name multiple times just to be sure ---- SORRRRRRY!

 

[PHV]

You didn't answer to this:
frmReportSelect must be an open mainform at the time the query is launched.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PeanutB7]

PHV

Thanks for the quick response but I am still getting the same result; two small parameter dialog boxes with:

Forms!frmReportSelect!txtStartDate' and
Forms!frmReportSelect!txtEndDate'

The SQL is not recognizing or identifying the form that I have created. I did verify the name of the form multiple times to eliminate another possibility. Please think about the situation if you get a chance.

Thank you once again!!!!

JB

 

[PeanutB7]

Sorry PHV,

I did not see your last post when I checked the last time.

I did not realize that the form had to be open at the time of the querry. I will have to figure a way sequentially to have that occur. I already have a switchboard form with multiple queries and reports assembled to organize and provide direction to the user. I will probably use the command button for the querry request to open the form and then the query can take it from there. I figured it out while I was typing. Thank you once again for all your time and thought into my situation. I TRULY appreciate your assistance. Now please take the rest of the night off.

Thanks again,

JB