Query By Paramater

[fraser_lindsay]

Query By Parameter

Hello,

I am trying to set up a query by paramater to then base a report on.

For example, I would like to input a location name to get a query to only display the records for the specifiied location and the accompanying data for use in the report.

How do I do this?

I have tried using the [Enter .......] type command but I either get zero or I get the full list, which is no use.

Thanks

 

[ScottGem]

It depends on where you are putting the [Enter....].

In a well constructed database, Locations names would only be stored in one table. That table would be a table of locations and information about them. Each location would be identified by a primary key, preferably an autonumber. This LocationID would then be used as a foreign key in any other tables where the location needs to be stored.

In such a case I would create a form with a Combobox to select the Location. The combobox would present the list of names, but store the LocationID. You would then set the crtiteria for the LocationID column in your query to:

=Forms!formname!controlname

 

[fraser_lindsay]

Thansk very much for your reply.

It depends on where you are putting the [Enter....].

In a well constructed database, Locations names would only be stored in one table. That table would be a table of locations and information about them. Each location would be identified by a primary key, preferably an autonumber. This LocationID would then be used as a foreign key in any other tables where the location needs to be stored.

Yep, I have that part under control.

In such a case I would create a form with a Combobox to select the Location. The combobox would present the list of names, but store the LocationID. You would then set the crtiteria for the LocationID column in your query to:

I have created a form and used the wizard to create a combo box linked to the Location ID field. It lists the correct locations but does not link to anything.

=Forms!formname!controlname

I have set the criteria in my query to:

[Forms]![frmFilterBySelection]![Location]


I still get nothing. Have I done something wrong with the string in the criteria box?

Thanks

 

[fraser_lindsay]

It's all fine. I am in fact quite stupid.

I was messing around with the auto object function. By selecting the location in my drop down and selecting autoreport it generates a nice shiny report with all the relevant names as per my query.

I guess all I need to do is make a report template and put a report button on the form.

 

[ScottGem]

Check your query. If everything is construcuted as I suggested then the query should only return records for the selected location. Then use that query as the Recordsource for your report.

 

[fraser_lindsay]

Scott,

It's rather strange. When I run the query and select the desired location, the query doesn't show any results.

However, if I load the form that the query is based on as you suggested. Then select the location in the combo box and then click the preview report button to load my report template I get the filtered results showing as I wanted.

So it works but I don't why why. Any ideas?


I have also extended the query slightly to include 'Business Unit' and the accompanying forms. So now I select the Business Unit and the Location.

I have one more question - how do I link these two fields? I want to select from a list of locations only specific to one business unit, rather than the full list. Do I need to join my separate [Location] and [Business Unit] tables or modify relationships somewhere?

Thanks for all you help so far.

 

[ScottGem]

Scott,

It's rather strange. When I run the query and select the desired location, the query doesn't show any results.

However, if I load the form that the query is based on as you suggested. Then select the location in the combo box and then click the preview report button to load my report template I get the filtered results showing as I wanted.

So it works but I don't why why. Any ideas?.

I'm going to make a guess here, but if you run the query with the form being closed, it prompts you showing Forms!formname!controlname. You then enter the Location name. But the criteria is in the LocationID column. The combobox is returning the ID, not the name. That's why using the form would work and just typing in the Name wouldn't. look at the properties of the combo (specifically the bound column) and where the criteria is in the query.

I have also extended the query slightly to include 'Business Unit' and the accompanying forms. So now I select the Business Unit and the Location.

I have one more question - how do I link these two fields? I want to select from a list of locations only specific to one business unit, rather than the full list. Do I need to join my separate [Location] and [Business Unit] tables or modify relationships somewhere?

Thanks for all you help so far.

This is a common technique called Syncronized or Cascading combos. If you search here on either of those phrases you will find info on how to do it.

 

[fraser_lindsay]

Your guess was spot on.

What am I looking for in the properties? there are two columns and it is bound to column 2, which is the location names and not the IDs which is what I want.

Do I need to change it? Or were you just explaining why it did what it does?

Thanks for the prompt on cascading combo's. I'm looking into that now.

Thanks,

F

 

[ScottGem]

Look at your Rowsource. its probably something like:

SELECT LocationID, LocationName FROM tblLocations ORDER BY LocationName;

Therefore you want the bound column to be 1. You want the query to have the criteria in the LocationID column.