Enter MULTIPLE Items in a Query (e.g. [Name:] ??

[kev100 via AccessMonster.com]

I've got a query set up that will return all the entries relating to a
particular Name.

The names are stored in the "Name" field.

In the Query, I simply used the [Enter First Name:] so that the user is
prompted.


Entering 1 name works great.

However, what statement do I use in the Query to allow 1 OR More to be
entered....so that it will return for one or all (basically Name1 or Name2 or
Name3.......etc)?

I've tried just entering "John" or "Mary" or "Tom" into the existing prompt,
but it doesn't seem to work.

Thanks

 

[Duane Hookom]

As you have seen, you can't enter more than one name using your current
syntax. I would first change the name name to some other name so Access
doesn't confuse an object name "property" with the name of a field.

The only non-code method that I am aware of that can use a parameter prompt
is to set up a where clause like:

WHERE Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",")

0

--
Duane Hookom
MS Access MVP
--

I've got a query set up that will return all the entries relating to a
particular Name.

The names are stored in the "Name" field.

In the Query, I simply used the [Enter First Name:] so that the user is
prompted.


Entering 1 name works great.

However, what statement do I use in the Query to allow 1 OR More to be
entered....so that it will return for one or all (basically Name1 or Name2
or
Name3.......etc)?

I've tried just entering "John" or "Mary" or "Tom" into the existing
prompt,
but it doesn't seem to work.

Thanks

 

[Tom Ellison]

Dear Kev:

Duane's response is a good one, and is simple. It is not the most powerful
or useful one.

A good approach would be to create a multi-select list box of all the first
name values in the table. The user could then select every one that is
desired. An advantage to this is that the user has the possibility of
seeing all the first names in the table. Sometimes this can be very useful
in helping the user to realize what kinds of spellings abound. Maybe
someone told the user to look for "John". Will the user know to look for
"Jon" as well?

When creating software to search for something, it is best not to have users
spell the thing they are looking for. Many user aren't that good at this
anyway. Picking something from a list is easier and more likely to be
successful.

An additional big advantage occurs if the user wishes to repeat the search,
but adding or deleting one or more of the previous selections. With a
prompt, the user will have to retype everything. With a control, the user
can just click to add or remove selections.

Once the selections have been made, the user clicks a command button. The
code you write there builds the SQL from the selections made.

Tom Ellison

 

[kev100 via AccessMonster.com]

Thanks very much for that prompt. I think that will work great. However, I
did a copy and paste of:

WHERE Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",") >
0

....and edited for my specifics, but get a syntex error.

I also tried: WHERE Instr("," & [Enter first names] & ",", "," &
[FirstNameField] & ",")
....just in case that second line did not apply.


Also....The multi-selection suggestion would be neater, but there so MANY
different names that sorting through the choices would take more time that
just typing them in.


THANKS

 

[kev100 via AccessMonster.com]

Check that.....

I see the ">0" part is just part of the reply post format.

Using:

WHERE Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",")

....then editing for my specfics gets a syntax error.

My actual situation concerns Route numbers (I used the Name example to try to
be as simple as possible).

The field is actually named "route" and could contain 200+ different possible
numbers.

So....using

WHERE Instr("," & [Enter Route Numbers] & ",", "," & [route] & ",")

gets a syntax error.

Could my edits be causing a problem?

THANKS

 

[Duane Hookom]

I don't see where your syntax include the >0. Maybe you should post your
full SQL view.

 

[kev100 via AccessMonster.com]

In my First reply, I suggested that one of the criteria lines I tried
included the ">0" (in addition to a line without).

I quickly realized that the ">0" was just part of posting a reply on the
board and not part of the criteria line that you suggested. I just posted
the second reply to let you know that I had realized that and that I was no
longer including the ">0".

So....eliminating the ">0" issue completely.....

Copying and pasting the crteria line:

WHERE Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",")

....then editing for my specfics returns a syntax error.


The line I actually used after editing for my specifics is (name of the db
field is "route"):

WHERE Instr("," & [Enter Route Numbers] & ",", "," & [route] & ",")

It too gets a syntax error.

Could my edits be causing a problem?

Thanks very much for the follow-ups
KEV

 

[John Spencer]

You do need the greater than zero.

WHERE Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",")>0

Also, you need to enter the list of names, separated by commas, and NO spaces.

 

[kev100 via AccessMonster.com]

You do need the greater than zero.

WHERE Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",")>0

Also, you need to enter the list of names, separated by commas, and NO spaces.

I see......

I had interpreted the [Enter first names] as what would trigger the pop-up
prompt (assuming that maybe the : colon was mistakenly left out).

I should have been more clear about this point. I Need the query to Prompt
the user to input the route values.

Currently, I have "[Enter Route Number:]" in the criteria line, and the query
will return all records with that route number. HOWEVER, I need the pop-up
prompt to handle 1 or MORE route #'s.

So....if the user wanted to see all records for router 12, they could just
enter 12 in the pop up prompt. If they needed to see all records in routes
12, 34, 50, they could enter those numbers (in some format) and see all
records with those route numbers.

I cannot pre-code in a certain set of routes in the criteria line because a
user may need to see any 1 or any group of routes at any given time.

Thanks,
Kev

 

[Duane Hookom]

Again, post your full SQL view. In the design view, the Field will show
Expr1:Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",")

and the criteria will be

0

--
Duane Hookom
MS Access MVP
--

You do need the greater than zero.

WHERE Instr("," & [Enter first names] & ",", "," & [FirstNameField] &
",")>0

Also, you need to enter the list of names, separated by commas, and NO
spaces.

I see......

I had interpreted the [Enter first names] as what would trigger the pop-up
prompt (assuming that maybe the : colon was mistakenly left out).

I should have been more clear about this point. I Need the query to
Prompt
the user to input the route values.

Currently, I have "[Enter Route Number:]" in the criteria line, and the
query
will return all records with that route number. HOWEVER, I need the
pop-up
prompt to handle 1 or MORE route #'s.

So....if the user wanted to see all records for router 12, they could just
enter 12 in the pop up prompt. If they needed to see all records in
routes
12, 34, 50, they could enter those numbers (in some format) and see all
records with those route numbers.

I cannot pre-code in a certain set of routes in the criteria line because
a
user may need to see any 1 or any group of routes at any given time.

Thanks,
Kev

 

[kev100 via AccessMonster.com]

Again, post your full SQL view. In the design view, the Field will show
Expr1:Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",")

and the criteria will be

0

Will do. I am not at that computer now, but will be later.

But, to be sure, I understand correctly.....the area above "[Enter first
names] " will PROMPT the user when the query is run....and is Not an area
where I need to actually enter certain names (routes, in my case) ahead of
time?

Because, again,

I need the pop-up prompt to handle 1 or MORE route #'s.

IOW, if the user wanted to see all records for router 12, they could just
enter 12 in the pop up prompt. If they needed to see all records in routes
12, 34, 50, they could enter those numbers (in some format) and see all
records with those route numbers.

I cannot pre-code in a certain set of routes in the criteria line because a
user may need to see any 1 or any group of routes at any given time.

Thanks

 

[Duane Hookom]

The user running the query will be prompted for first names. They must be
entered exactly as stored in the first name field and must have a comma
between each.

BTW: I would never use this solution as it is prone to data entry errors and
frustrations. I would create a form with a multi-select list box as
suggested by Tom Ellison very early in this thread.

--
Duane Hookom
MS Access MVP
--

Again, post your full SQL view. In the design view, the Field will show
Expr1:Instr("," & [Enter first names] & ",", "," & [FirstNameField] & ",")

and the criteria will be

0

You do need the greater than zero.

Will do. I am not at that computer now, but will be later.

But, to be sure, I understand correctly.....the area above "[Enter first
names] " will PROMPT the user when the query is run....and is Not an area
where I need to actually enter certain names (routes, in my case) ahead of
time?

Because, again,

I need the pop-up prompt to handle 1 or MORE route #'s.

IOW, if the user wanted to see all records for router 12, they could just
enter 12 in the pop up prompt. If they needed to see all records in
routes
12, 34, 50, they could enter those numbers (in some format) and see all
records with those route numbers.

I cannot pre-code in a certain set of routes in the criteria line because
a
user may need to see any 1 or any group of routes at any given time.

Thanks

 

[Guest]

Dear Tom

I have a very similar request as Kev. I need to create a prompt which will
asks for ID numbers, so I can produce a report of contact details of a
certain number of employees (always varying) which I can then email off for
another contractor. In other words, every job that comes up, requires me to
pull together certain employees into a single report which i need to send off
containing contact details of certain employees based on their ID numbers.
How may I do this, or what altenatives do I have?
Thank you.
Rigby

Dear Kev:

Duane's response is a good one, and is simple. It is not the most powerful
or useful one.

A good approach would be to create a multi-select list box of all the first
name values in the table. The user could then select every one that is
desired. An advantage to this is that the user has the possibility of
seeing all the first names in the table. Sometimes this can be very useful
in helping the user to realize what kinds of spellings abound. Maybe
someone told the user to look for "John". Will the user know to look for
"Jon" as well?

When creating software to search for something, it is best not to have users
spell the thing they are looking for. Many user aren't that good at this
anyway. Picking something from a list is easier and more likely to be
successful.

An additional big advantage occurs if the user wishes to repeat the search,
but adding or deleting one or more of the previous selections. With a
prompt, the user will have to retype everything. With a control, the user
can just click to add or remove selections.

Once the selections have been made, the user clicks a command button. The
code you write there builds the SQL from the selections made.

Tom Ellison

I've got a query set up that will return all the entries relating to a
particular Name.

The names are stored in the "Name" field.

In the Query, I simply used the [Enter First Name:] so that the user is
prompted.


Entering 1 name works great.

However, what statement do I use in the Query to allow 1 OR More to be
entered....so that it will return for one or all (basically Name1 or Name2
or
Name3.......etc)?

I've tried just entering "John" or "Mary" or "Tom" into the existing
prompt,
but it doesn't seem to work.

Thanks

 

[Duane Hookom]

Did you read the replies to see if you can apply the suggested solution?

--
Duane Hookom
MS Access MVP
--

Dear Tom

I have a very similar request as Kev. I need to create a prompt which will
asks for ID numbers, so I can produce a report of contact details of a
certain number of employees (always varying) which I can then email off
for
another contractor. In other words, every job that comes up, requires me
to
pull together certain employees into a single report which i need to send
off
containing contact details of certain employees based on their ID numbers.
How may I do this, or what altenatives do I have?
Thank you.
Rigby

Dear Kev:

Duane's response is a good one, and is simple. It is not the most
powerful
or useful one.

A good approach would be to create a multi-select list box of all the
first
name values in the table. The user could then select every one that is
desired. An advantage to this is that the user has the possibility of
seeing all the first names in the table. Sometimes this can be very
useful
in helping the user to realize what kinds of spellings abound. Maybe
someone told the user to look for "John". Will the user know to look for
"Jon" as well?

When creating software to search for something, it is best not to have
users
spell the thing they are looking for. Many user aren't that good at this
anyway. Picking something from a list is easier and more likely to be
successful.

An additional big advantage occurs if the user wishes to repeat the
search,
but adding or deleting one or more of the previous selections. With a
prompt, the user will have to retype everything. With a control, the
user
can just click to add or remove selections.

Once the selections have been made, the user clicks a command button.
The
code you write there builds the SQL from the selections made.

Tom Ellison

I've got a query set up that will return all the entries relating to a
particular Name.

The names are stored in the "Name" field.

In the Query, I simply used the [Enter First Name:] so that the user is
prompted.


Entering 1 name works great.

However, what statement do I use in the Query to allow 1 OR More to be
entered....so that it will return for one or all (basically Name1 or
Name2
or
Name3.......etc)?

I've tried just entering "John" or "Mary" or "Tom" into the existing
prompt,
but it doesn't seem to work.

Thanks

 

[Tom Ellison]

Dear Rigby:

For this, I recommend you use a multi-select list box. From your
description, I guess you would put the ID numbers in the list. Do you
expect your users to know all the employees by their ID numbers?

You will then need to code an IN() statement in your query built of all the
selected employees' ID numbers.

I would have thought it better to use the employees' names. But, if you
wish to use this, the names must be enforced to be unique. Have you done
that? If the names are unique, then you don't need an ID number for this.
It just gets in the way. If you have not made the employees' names unique,
then you may allow a user to "add" an employee who is already entered.
Entering the same person twice into the database may be a bad thing.
Correction, it would definitely be a very bad thing. For that reason, using
a unique constraint on the natural key (the name) is the first (but not
only) line of defense.

Tom Ellison

Dear Tom

I have a very similar request as Kev. I need to create a prompt which will
asks for ID numbers, so I can produce a report of contact details of a
certain number of employees (always varying) which I can then email off
for
another contractor. In other words, every job that comes up, requires me
to
pull together certain employees into a single report which i need to send
off
containing contact details of certain employees based on their ID numbers.
How may I do this, or what altenatives do I have?
Thank you.
Rigby

Dear Kev:

Duane's response is a good one, and is simple. It is not the most
powerful
or useful one.

A good approach would be to create a multi-select list box of all the
first
name values in the table. The user could then select every one that is
desired. An advantage to this is that the user has the possibility of
seeing all the first names in the table. Sometimes this can be very
useful
in helping the user to realize what kinds of spellings abound. Maybe
someone told the user to look for "John". Will the user know to look for
"Jon" as well?

When creating software to search for something, it is best not to have
users
spell the thing they are looking for. Many user aren't that good at this
anyway. Picking something from a list is easier and more likely to be
successful.

An additional big advantage occurs if the user wishes to repeat the
search,
but adding or deleting one or more of the previous selections. With a
prompt, the user will have to retype everything. With a control, the
user
can just click to add or remove selections.

Once the selections have been made, the user clicks a command button.
The
code you write there builds the SQL from the selections made.

Tom Ellison

I've got a query set up that will return all the entries relating to a
particular Name.

The names are stored in the "Name" field.

In the Query, I simply used the [Enter First Name:] so that the user is
prompted.


Entering 1 name works great.

However, what statement do I use in the Query to allow 1 OR More to be
entered....so that it will return for one or all (basically Name1 or
Name2
or
Name3.......etc)?

I've tried just entering "John" or "Mary" or "Tom" into the existing
prompt,
but it doesn't seem to work.

Thanks

 

[kev100 via AccessMonster.com]

The user running the query will be prompted for first names. They must be
entered exactly as stored in the first name field and must have a comma
between each.

BTW: I would never use this solution as it is prone to data entry errors and
frustrations. I would create a form with a multi-select list box as
suggested by Tom Ellison very early in this thread.

I agree as well, in most cases, however, in this particular application the
"names" will actually be unique numbers between 1 and 3 digits long. A pull-
down option would end up being just a giant list, hundreds of numbers long.


Here's the SQL view of that query.

SELECT results.route
FROM results
WHERE (((results.route)=[Route:]))
ORDER BY results.route;



The table name is "results"

The field that is being querried on is "route"

Currently, the user is prompted to enter the Route name (which is actually a
number) they want to see all records for. However, they can only enter 1
route at a time.

They need to be able to enter 1 or More routes and have the query show all
entered. Seperating each by a comma will not be a problem. Since route
"names" are actually numbers, the chance of entry errors will hopefully be
reduced..

Thanks