Parameter Query 2

[Brenwin]

I have a query that enables the user to input a start and end date into it, upon which several other queries perform calculations. I need the dates to be user input as they will vary from user to user.

Unfortunately, each one of the sub queries chucks up a parameter entry box asking for the start and end dates, even when I have the original query open with dates entered.

Is there any way to make the sub queries use the data entered in the top query, and not ask for input?

I have a feeling I'm missing something simple.....

 

[dhookom]

The better method is to use controls on forms for criteria rather than parameter prompts.

Controls allow for seeing multiple criteria values at once, checking for integrity or nulls, combo/list boxes, date pickers,... Plus, users won't get prompted multiple times.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Brenwin]

Chuffin' nora - you don't hang about, do you? Well impressive.

So - if I set up two controls on a form that allows the user to choose from a list of start and end dates, those chosen two dates will be used by a query for all the records in that query?

Is that along the lines of:

[frmEnterRelevantDates]![StartDate] in the criteria box of the query?

I can smell success!

 

[PHV]

>=[Forms]![frmEnterRelevantDates]![StartDate]

frmEnterRelevantDates must be an open main form.

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

 

[Brenwin]

Right, sorry - didn't explain myself adequately. My sincere apologies.

I don't want to find records that already exist based on a date parameter chosen by a user, I want the dates that the user specifies to be added to the fields in the records in the query to be used in subsequent calculations for every record (as defined by fixed parameters within the rest of the query).

I have done this by entering StartDate:#4/4/04# and EndDate:#7/4/04# in the Field sections of the Qry in design view - this drills down the 4th April 04 into the StartDate field of every record in the query, and the same for EndDate, and is then used for subsequent calculations. The query then checks which records were present ( via Move in and Move out dates for residents in a homeless hostel) for the period defined by StartDate and EndDate.

This works, but the dates/period needs to change often, and with my method, changing the dates would require the Qry to be opened in design view, and this can't happen since the users know even less than I about Access.

I need a method, therefore, whereby the user gets to choose a StartDate and EndDate which are then placed in the appropriate StartDate and EndDate fields of every record of the Qry - replacing what's in there previously. I don't need to save the results, since they are exported to a spreadsheet by subsequent queries, and can always be repeated.

I agree that using a Query-by-Form would be the best way, but again, all the help I can find on this is for querying records with fields that already have data in them, but I want what the user chooses to be put in the fields of all the records.

If I haven't made myself clear this time, I'm going to do the decent thing with a tumbler of scotch and a revolver in my office.

Sorry for being a pest.

 

[dhookom]

You can place the value from the controls in your query by creating columns with fields expressions like:

StartDate:[Forms]![frmEnterRelevantDates]![StartDate]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Brenwin]

Sorted.

Many thanks to you, dhookom, and to you, PHV (should have persisted with your first answer.)

Stars to you both