Syntax used within Criteria of Query Builder to narrow options

[Birky]

Can you please help me understand how to build Criteria syntax within the Query Builder?

If you have a field that is for a region (lets say for Midwest) and you have another field which breaks it down into a smaller geographical region like City (lets say Denver) how can I build the Criteria within the Query Builder to key off a previous select field to narrow down the location?

If
region Midwest is select within region field
THEN
only show cities within that region.

I currently have a Many to Many flow in which I am using a join table to tie them together. I just need to know the syntax for the Criteria on how to use a value from a previous selected field to narrow down the options in the next field. I am using combo boxes to drop down the appropriate options (well hopefully show the appropriate options).

Any help would be greatly appreciated.

Thanks
Mike

[NeoPa]

I don't understand how you can have a combobox available when you are designing a query. That is a control on a form.
It is important that you're clear as to exactly what you're looking for.
I'm fairly confident we know the answer to your question. It's just a matter now of expressing the question clearly and in enough detail.

[MMcCarthy]

Can you please help me understand how to build Criteria syntax within the Query Builder?

If you have a field that is for a region (lets say for Midwest) and you have another field which breaks it down into a smaller geographical region like City (lets say Denver) how can I build the Criteria within the Query Builder to key off a previous select field to narrow down the location?

If
region Midwest is select within region field
THEN
only show cities within that region.

I currently have a Many to Many flow in which I am using a join table to tie them together. I just need to know the syntax for the Criteria on how to use a value from a previous selected field to narrow down the options in the next field. I am using combo boxes to drop down the appropriate options (well hopefully show the appropriate options).

Any help would be greatly appreciated.

Thanks
Mike

OK Mike

The Lookup lists from the table are not available in the query builder in the way you think. However, you can use criteria to restrict the data. If you give us the table name and field names and tell us what yuo want to return in the query we will help you work it out.

Mary

[Birky]

OK Mike

The Lookup lists from the table are not available in the query builder in the way you think. However, you can use criteria to restrict the data. If you give us the table name and field names and tell us what yuo want to return in the query we will help you work it out.

Mary

Here is what I have:

Table name = Instance
field names:
Instance
Purpose
Designation
Notes


Table name = Schema
Field names:
Schema
Purpose
Notes


Which are tied together with a junction table named = Join_Inst_Schem
Field names:
Instance
Schema

Since I have a many to many relationship with these two tables I need the join table to do some linking for me. I plan to create a new table to show events for when new components are loaded within a schema. therefore I plan to have another table named Event_Log, with the appropriate field names from the above table within.

If I have an Instance, and Schema combo box within the form how can I get the Schema box to only show Schema's in which I have linked together with a particular Instance within the Join_Inst_Schem table?

If I have Instance "Midwest" tied to only two or these Schemas within the join table and I select "Midwest" within the Instance dropdown I would like the Schema drop down to only show the values tied to that Instance.

I hope that makes sense, being new to this I'm not sure if I'm using the right terminology or not to get my point across.

Again, any help would be greatly appreciated.

[MMcCarthy]

Here is what I have:

Table name = Instance
field names:
Instance
Purpose
Designation
Notes


Table name = Schema
Field names:
Schema
Purpose
Notes


Which are tied together with a junction table named = Join_Inst_Schem
Field names:
Instance
Schema

Since I have a many to many relationship with these two tables I need the join table to do some linking for me. I plan to create a new table to show events for when new components are loaded within a schema. therefore I plan to have another table named Event_Log, with the appropriate field names from the above table within.

If I have an Instance, and Schema combo box within the form how can I get the Schema box to only show Schema's in which I have linked together with a particular Instance within the Join_Inst_Schem table?

If I have Instance "Midwest" tied to only two or these Schemas within the join table and I select "Midwest" within the Instance dropdown I would like the Schema drop down to only show the values tied to that Instance.

I hope that makes sense, being new to this I'm not sure if I'm using the right terminology or not to get my point across.

Again, any help would be greatly appreciated.

OK Birky

I'm not really sure what you are looking for. One suggestion I have is to have the combo list based on a query to return the values to the drop down list you require. Then have a subform in data sheet format based on all the records and set a filter based on the selected value in the combo box to restrict the records shown.

If this is the kind of thing you are looking for we can go into more detail.

Mary

[Birky]

OK Birky

I'm not really sure what you are looking for. One suggestion I have is to have the combo list based on a query to return the values to the drop down list you require. Then have a subform in data sheet format based on all the records and set a filter based on the selected value in the combo box to restrict the records shown.

If this is the kind of thing you are looking for we can go into more detail.

Mary

I’m just trying to narrow down the options for the user depending on what they had selected within a previous drop down.

If they select “Midwest” then I’d like to narrow down the options for the next combo field to whatever “Cities” fall within the Midwest region.

If no region is selected then they get all the Cities contained within the table, but if they select a region I need to narrow the cities down those that fall within.

[NeoPa]

In that case Birky, you need to create a query (or some SQL) to set the first dropdown to match all the regions in your main table.
The second needs to have some SQL which may be updated by code in the AfterUpdate of the first (or alternatively be generically set up to refer to the other dropdown - can't use Me. here though) one.
The subform needs to have a query or SQL set up to filter by the second dropdown if set, otherwise the first if set, otherwise nothing.
Does this help any?

[Birky]

Understood, that brings us back around to the question, how do I construct the SQL statement for the second dropdown to key off whatever data was selected in the first? How can I, or can you, pass a variable from the first selection into the where clause of the second drop down??

select cities from city where region = <??????>;

I’m assuming the <??????> can be done but I do not know the syntax.

Appreciate you help..
Mike

[MMcCarthy]

Understood, that brings us back around to the question, how do I construct the SQL statement for the second dropdown to key off whatever data was selected in the first? How can I, or can you, pass a variable from the first selection into the where clause of the second drop down??

select cities from city where region = <??????>;

I’m assuming the <??????> can be done but I do not know the syntax.

Appreciate you help..
Mike

Hi Mike

In the second combo

SELECT cities FROM city where REGION = [Forms]![FormName]![Combo1Name];

In the after update event of the first combo. Put

Me.Combo2Name.Requery

Mary

[Birky]

Mary,

I knew I was missing the boat on this one.. Thanks SOOOOO much for the help, it is truly appreciated. (if you were here I’d kiss ya). I have been working on this one for a long time and you were able to clear it up in seconds..

Thanks Again and have a Merry Christmas.

Birky