newbie: Using form field as SELECT parameter

[ButBi]

I have a table with following field:
Respondent, Indicator1, Indicator2,.. IndicatorN, Country

I want to create a query to compare respondents from 1 country in one
indicator.

I create a form with 2 list boxes: indicator and country
How can I pass the value of indicator field to SELECT and country field
to WHERE parameter?

And how if I want to choose more then one indicator?

I know it may be a newbie question but I don't find proper answer to my
problem using search function on this group.

Thank you for any help.

 

[Lachlan Mullen]

In your SQL View, reference the form fields like this:

[Forms]![formname].[fieldname]

Does that answer your question?

Lach

 

[John Vinson]

I have a table with following field:
Respondent, Indicator1, Indicator2,.. IndicatorN, Country

Then you have an incorrectly designed table. "Fields are expensive,
records are cheap" - if you have a one (respondent) to many
(indicators) relationship, the correct table design would have THREE
tables:

Respondents
RespondentID <primary key, maybe an autonumber>
RespondentName
Country
<any other needed info about the respondant>

Indicators
IndicatorNo <primary key, integer, 1 to N>
Description Text <what this indicator means>

Responses
RespondentID <link to Respondents>
IndicatorNo <link to Indicators>

I want to create a query to compare respondents from 1 country in one
indicator.

Very difficult with your current "spreadsheet" design - utterly
trivial with the normalized design.

I create a form with 2 list boxes: indicator and country
How can I pass the value of indicator field to SELECT and country field
to WHERE parameter?

You cannot do so. Fieldnames are not data, and cannot be passed as
parameters. (Country is easy of course).

And how if I want to choose more then one indicator?

Again - all but impossible with your design; with the normalized
design you'ld use a criterion of

IN (3, 8, 21, 44, 51, 98)

on the IndicatorNo field in Responses to select just those six
indicators.

John W. Vinson[MVP]

 

[peregenem]

with the normalized
design you'ld use a criterion of

IN (3, 8, 21, 44, 51, 98)

on the IndicatorNo field in Responses to select just those six
indicators.

Sorry but how to pass the list of indicators to the parameter?

 

[ButBi]

Thank you for your replies.
Maybe I have to explain more here. I have the normalized database
structure like you said (At Your Survey by Duane). Actually the "table"
I mentioned above is a query with calculated data. In ASP, VBScript I
can do it easly to pick the SELECT parameters I need from a form.
What is the way in Acces to choose SELECT parameter.
Thanks for any hints.

 

[John Vinson]

What is the way in Acces to choose SELECT parameter.

If you need to use a fieldname as a parameter, your only choice is to
write VBA code to construct the SQL string containing the fieldname.
It would be rather like your VBScript, I'm guessing.

John W. Vinson[MVP]