ENTER MULTIPLE Items in a Query Pop-Up PROMPT e.g. [Name:]

[kev100 via AccessMonster.com]

** REPOST OF AN OLDER UNFINISHED THREAD **

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

[quoted text clipped - 3 lines]

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

 

[Duane Hookom]

There were a couple recent threads (including yours) where I suggested
syntax like:

SELECT results.route
FROM results
WHERE Instr("," & [Enter Routes with comma between] & ",", "," & [Route] &
",")>0
ORDER BY results.route;

This should work if a user entered "6,13,27"

--
Duane Hookom
MS Access MVP
--

** REPOST OF AN OLDER UNFINISHED THREAD **

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

[quoted text clipped - 3 lines]

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..

 

[kev100 via AccessMonster.com]

Thanks very much for that information.

I did a direct copy and past of those query lines. It will run without
errors, but produces an empty list.

These lines from a Query that will can be used for a SINGLE route list....

SELECT [Results - Imported].route
FROM [Results - Imported]
WHERE ((([Results - Imported].route)=[Enter a Route:]))
ORDER BY [Results - Imported].route;


...work. For example, when run, the user can enter "50" at the prompt and
it will return all the entries for route 50. And when run again, the user
can enter "15" and it will return those entries.

But when...

SELECT results.route
FROM results
WHERE Instr("," & [Enter Routes with comma between] & ",","," & [Route] & ",")

0

ORDER BY results.route;

.....is used and the user enters "50, 15" only an empty lists results (a list
with no entries).

Have I mistyped something...or do I need to make any edits?

Thanks very much for your help.

There were a couple recent threads (including yours) where I suggested
syntax like:

SELECT results.route
FROM results
WHERE Instr("," & [Enter Routes with comma between] & ",", "," & [Route] &
",")>0
ORDER BY results.route;

This should work if a user entered "6,13,27"

 

[kev100 via AccessMonster.com]

** PLEASE IGNORE THAT FIRST REPLY TO YOUR POST.....THIS IS HOW IT SHOULD HAVE
READ ***

Thanks very much for that information.

I did a direct copy and past of those query lines. It will run without
errors, but produces an empty list.

These lines from a Query that will can be used for a SINGLE route list....

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

....work. For example, when run, the user can enter "50" at the prompt and
it will return all the entries for route 50. And when run again, the user
can enter "15" and it will return those entries.

But when...

SELECT results.route
FROM results
WHERE Instr("," & [Enter Routes with comma between] & ",","," & [Route] & ",")

0

ORDER BY results.route;

.....is used and the user enters "50, 15" only an empty lists results (a list
with no entries).

Have I mistyped something...or do I need to make any edits?

Thanks very much for your help.

 

[Duane Hookom]

Try without entering any spaces. I assume your users aren't actually
entering the quotes.

--
Duane Hookom
MS Access MVP
--

** PLEASE IGNORE THAT FIRST REPLY TO YOUR POST.....THIS IS HOW IT SHOULD
HAVE
READ ***

Thanks very much for that information.

I did a direct copy and past of those query lines. It will run without
errors, but produces an empty list.

These lines from a Query that will can be used for a SINGLE route list....

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

...work. For example, when run, the user can enter "50" at the prompt and
it will return all the entries for route 50. And when run again, the user
can enter "15" and it will return those entries.

But when...

SELECT results.route
FROM results
WHERE Instr("," & [Enter Routes with comma between] & ",","," & [Route] &
",")

0

ORDER BY results.route;

....is used and the user enters "50, 15" only an empty lists results (a
list
with no entries).

Have I mistyped something...or do I need to make any edits?

Thanks very much for your help.

 

[kev100 via AccessMonster.com]

Duane,

Thanks for that feedback.

I simply did a copy/paste of

SELECT results.route
FROM results
WHERE Instr("," & [Enter Routes with comma between] & ",","," & [Route] & ",")

0

ORDER BY results.route;

Into the SQL view of my query screen. While it will run, it will not return
any results. The page that the query returns is simply empty.

While looking closer....

I switched to Design View to see how the above appeared in the column.

It looks like those lines are breaking up across 2 columns, for some reason.
Here's what shows.

In Column 1 of Design View:

Field: route
Table: results
Sort: Ascending

Critera: (empty)


But...then in Column 2, this appears:

Field: InStr("," & [Enter Routes with comma between] & ",","," & [Route] & ",
")
Table:
Sort: (empty)

Critera: >"0"


Is it supposed to be spread over 2 colums or have I mis-typed something?

Thanks

 

[kev100 via AccessMonster.com]

Here's a slight correction to the above:

It looks like those lines are breaking up across 2 columns, for some reason.
Here's what shows.

In Column 1 of Design View:

Field: route
Table: results
Sort: Ascending

Critera: (empty)


But...then in Column 2, this appears:

Field: InStr("," & [Enter Routes with comma between] & ",","," & [Route] & ",
")
Table: (empty)
Sort: (empty)

Critera: >"0"


Is it supposed to be spread over 2 colums or have I mis-typed something?

Thanks

 

[Duane Hookom]

Try to get to your SQL view and copy and paste this in a reply.
At very least, the criteria should be
Critera: >0
not
Critera: >"0"

 

[kev100 via AccessMonster.com]

Thanks.....

Here is a copy and paste of my SQL View:

SELECT results.route
FROM results
WHERE Instr("," & [Enter Routes with comma between] & ",", "," & [Route] &
",")>0
ORDER BY results.route;


When Viewed in Design View, here's what appears:

In Column 1 of Design View:

Field: route
Table: results
Sort: Ascending

Critera: (empty)

But...then in Column 2, this appears:

InStr("," & [Enter Routes with comma between] & ",","," & [Route] & ",")
Table: (empty)
Sort: (empty)

Critera: >0


NOTE:

As a test, I created a simply Single entry query to make sure I was getting
hits on route numbers. The SQL view of this Single entry test query is:

SELECT results.route
FROM results
WHERE (((results.route)=[Enter Route#:]));


In testing, I ran the above test single entry and enter 14, and got several
hits. I ran it again and entered 15, which also got several hits.


However, when the multi-entry one is used (posted at the very top)....it DOES
run, but returns no hits when the following is entered:

14, 15

Tried again with:
14,15 (no spaces)

And again with:

14 15 (space but no comma)

None returned any hits (but it did run).

Thanks very much.

---------------------------------------------

Try to get to your SQL view and copy and paste this in a reply.
At very least, the criteria should be
Critera: >0
not
Critera: >"0"

Here's a slight correction to the above:

[quoted text clipped - 23 lines]

Thanks

 

[Duane Hookom]

I just tested this in Northwind with the Products table.
SELECT Products.*, InStr("," & [Enter Routes with comma between] & ",","," &
[ProductID] & ",") AS Expr1
FROM Products
WHERE InStr("," & [Enter Routes with comma between] & ",","," & [ProductID]
& ",")>0;

I also tried with the Customer table:
SELECT Customers.*, InStr("," & [Enter Routes with comma between] & ",",","
& [CustomerID] & ",") AS Expr1
FROM Customers
WHERE InStr("," & [Enter Routes with comma between] & ",","," & [CustomerID]
& ",")>0;

This worked exactly as I expected. Try it with Northwind and then try to
figure out how your query/table is different.



--
Duane Hookom
MS Access MVP
--

Thanks.....

Here is a copy and paste of my SQL View:

SELECT results.route
FROM results
WHERE Instr("," & [Enter Routes with comma between] & ",", "," & [Route] &
",")>0
ORDER BY results.route;


When Viewed in Design View, here's what appears:

In Column 1 of Design View:

Field: route
Table: results
Sort: Ascending

Critera: (empty)

But...then in Column 2, this appears:

InStr("," & [Enter Routes with comma between] & ",","," & [Route] & ",")
Table: (empty)
Sort: (empty)

Critera: >0


NOTE:

As a test, I created a simply Single entry query to make sure I was
getting
hits on route numbers. The SQL view of this Single entry test query is:

SELECT results.route
FROM results
WHERE (((results.route)=[Enter Route#:]));


In testing, I ran the above test single entry and enter 14, and got
several
hits. I ran it again and entered 15, which also got several hits.


However, when the multi-entry one is used (posted at the very top)....it
DOES
run, but returns no hits when the following is entered:

14, 15

Tried again with:
14,15 (no spaces)

And again with:

14 15 (space but no comma)

None returned any hits (but it did run).

Thanks very much.

---------------------------------------------

Try to get to your SQL view and copy and paste this in a reply.
At very least, the criteria should be
Critera: >0
not
Critera: >"0"

Here's a slight correction to the above:

[quoted text clipped - 23 lines]

Thanks

 

[kev100 via AccessMonster.com]

Thanks very much for that sample. I did get it to work by doing a copy/paste
and then editing for my specifics.

However, as odd as this sounds, when I try to replicate the same process on
the computer at work, it does not take. Things run, but, it simply produces
an empty results list. I even tried it with the exact same database, did a
copy/paste of your code and edited (just like I did at home).

But, the one I created AT HOME does function properly on the computer at work
(I just copied the enter .mdb file containing the data table and query).

Is it possible that a certian version of access is needed (or are particular
driver or setting) to properly Create this query?

I think the version at work and home are the same (Access 2002)

If so....on possible explanation is that the access version / settings /
access data drivers on the computer at work are slightly different.

Thanks

I just tested this in Northwind with the Products table.
SELECT Products.*, InStr("," & [Enter Routes with comma between] & ",","," &
[ProductID] & ",") AS Expr1
FROM Products
WHERE InStr("," & [Enter Routes with comma between] & ",","," & [ProductID]
& ",")>0;

I also tried with the Customer table:
SELECT Customers.*, InStr("," & [Enter Routes with comma between] & ",",","
& [CustomerID] & ",") AS Expr1
FROM Customers
WHERE InStr("," & [Enter Routes with comma between] & ",","," & [CustomerID]
& ",")>0;

This worked exactly as I expected. Try it with Northwind and then try to
figure out how your query/table is different.

Thanks.....

[quoted text clipped - 68 lines]