Entering Criteria For Parameter Query

[Guest]

I have a query that currently looks like this:

SELECT district, account, name
FROM DNSData
WHERE district in ("Districtname",etc)
ORDER BY account;

The actual district names are entered in place of "Districtname" for however
many districts are needed.

I would like to prompt for districtname and be able to enter in a many as
needed. I tried to create a parameter that prompts for districtname but when
I enter in data I get results back for a single districtname but nothing is
returned when I enter multiple districtnames.

Example:

Where district in ([Enter District Name(s):])

[Enter District Name(s):] monroe - works ok
[Enter District Name(s):] monroe,desoto - returns nothing

What I am doing wrong? Thanks for your help.

Mark

 

[Guest]

You are expecting the parameter to evaluate as individual text strings, when
it actually gets interpreted as a single string. So when you enter:

monroe, desoto

at the parameter prompt, Access interprets this as "monroe, desoto", as a
single entity. You might think that if you entered it as:

"monroe", "desoto"

that Access would interpret it the way you want, but it doesn't.

Generally, the way to do this is to use a form with a multi select listbox
to allow the user to select multiple district names. Then when you click a
command button, the SQL gets built in VBA code and the query is run. Google
on "VBA +multiselect +listbox +query" and look through some of the links to
get a code example.

HTH
Dale

 

[Guest]

Thanks Dale for your response. I'll look into your suggestion.

Mark

You are expecting the parameter to evaluate as individual text strings, when
it actually gets interpreted as a single string. So when you enter:

monroe, desoto

at the parameter prompt, Access interprets this as "monroe, desoto", as a
single entity. You might think that if you entered it as:

"monroe", "desoto"

that Access would interpret it the way you want, but it doesn't.

Generally, the way to do this is to use a form with a multi select listbox
to allow the user to select multiple district names. Then when you click a
command button, the SQL gets built in VBA code and the query is run. Google
on "VBA +multiselect +listbox +query" and look through some of the links to
get a code example.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I have a query that currently looks like this:

SELECT district, account, name
FROM DNSData
WHERE district in ("Districtname",etc)
ORDER BY account;

The actual district names are entered in place of "Districtname" for however
many districts are needed.

I would like to prompt for districtname and be able to enter in a many as
needed. I tried to create a parameter that prompts for districtname but when
I enter in data I get results back for a single districtname but nothing is
returned when I enter multiple districtnames.

Example:

Where district in ([Enter District Name(s):])

[Enter District Name(s):] monroe - works ok
[Enter District Name(s):] monroe,desoto - returns nothing

What I am doing wrong? Thanks for your help.

Mark