Multiple parameters

[LizJ]

Can someone help. I need to retrieve peolple's addresses from a table. The
only criteria I can use to identify these people is their ID number (an auto
number field), there are no similarities i.e. their addresses aren't the
same, and the numbers aren't consecutive.

Can I use a parameter query to specify the different ID numbers, I might hav
between 6 and 12 people that I need to retrieve the addresses for.

Thanks

Liz

 

[Tom Ellison]

Dear Liz:

There is a way. It's not beautiful, but it will work.

To use this, the user must type in all the IDs without any mistake, and
place a comma (or any other separator you might choose) between the numbers.
The user must not put any spaces in.

For the search, a where clause might be:

WHERE InStr("," & [Enter ID:] & ",", "," & ID & ",") > 0

If you want to search for 123, 134, and 145, the user types 123,134,145 in
the parameter. The search then looks at an id, say 234, like this:

InStr(",123,134,145,", ",234,")

All this funny stuff with the commas is necessary so it doesn't find 234 in
12345.

This is not pretty. But neither are your circumstances. Searching a table
by a list of IDs sounds like a mess to me!

Tom Ellison

 

[LizJ]

Thanks Tom, I've pasted this into my query, but it's returning an error of
invalid syntax "you may have entered an operand without an operator".

The reason I have to search the table in this way is because all of these
people are enrolling for a course and the provider needs me to give him the
addresses. I have a separate table that records the attendances of the
students at the courses, however, I don't want to use this as they mightn't
turn up. I've then got to remove that instance from the table.

Dear Liz:

There is a way. It's not beautiful, but it will work.

To use this, the user must type in all the IDs without any mistake, and
place a comma (or any other separator you might choose) between the numbers.
The user must not put any spaces in.

For the search, a where clause might be:

WHERE InStr("," & [Enter ID:] & ",", "," & ID & ",") > 0

If you want to search for 123, 134, and 145, the user types 123,134,145 in
the parameter. The search then looks at an id, say 234, like this:

InStr(",123,134,145,", ",234,")

All this funny stuff with the commas is necessary so it doesn't find 234 in
12345.

This is not pretty. But neither are your circumstances. Searching a table
by a list of IDs sounds like a mess to me!

Tom Ellison

Can someone help. I need to retrieve peolple's addresses from a table.
The
only criteria I can use to identify these people is their ID number (an
auto
number field), there are no similarities i.e. their addresses aren't the
same, and the numbers aren't consecutive.

Can I use a parameter query to specify the different ID numbers, I might
hav
between 6 and 12 people that I need to retrieve the addresses for.

Thanks

Liz

 

[Michel Walsh]

Hi,



You can also use the operator LIKE, which does not require VBA, and allows
the use of a coma OR A SPACE (which many typists automatically add after a
coma) as delimiter:


WHERE ( ',' & [Enter ID's] & ',' ) LIKE
( '*[, ]' & IDfieldName & '[, ]*' )




Sure, if there are many choices, why not using a temporary table, with one
row by "parameter", and then, use an inner join:


SELECT whatever
FROM myTable INNER JOIN tempTable
ON myTable.ID = tempTable.ID



since then, the index on myTable.ID could be used, so decreasing the
execution time.



Vanderghast, Access MVP

Dear Liz:

There is a way. It's not beautiful, but it will work.

To use this, the user must type in all the IDs without any mistake, and
place a comma (or any other separator you might choose) between the
numbers. The user must not put any spaces in.

For the search, a where clause might be:

WHERE InStr("," & [Enter ID:] & ",", "," & ID & ",") > 0

If you want to search for 123, 134, and 145, the user types 123,134,145 in
the parameter. The search then looks at an id, say 234, like this:

InStr(",123,134,145,", ",234,")

All this funny stuff with the commas is necessary so it doesn't find 234
in 12345.

This is not pretty. But neither are your circumstances. Searching a
table by a list of IDs sounds like a mess to me!

Tom Ellison

Can someone help. I need to retrieve peolple's addresses from a table.
The
only criteria I can use to identify these people is their ID number (an
auto
number field), there are no similarities i.e. their addresses aren't the
same, and the numbers aren't consecutive.

Can I use a parameter query to specify the different ID numbers, I might
hav
between 6 and 12 people that I need to retrieve the addresses for.

Thanks

Liz

 

[Duane Hookom]

Reply with your SQL view that returned the error. Tom and others can't be of
much help without seeing what you have done.

--
Duane Hookom
MS Access MVP
--

Thanks Tom, I've pasted this into my query, but it's returning an error of
invalid syntax "you may have entered an operand without an operator".

The reason I have to search the table in this way is because all of these
people are enrolling for a course and the provider needs me to give him
the
addresses. I have a separate table that records the attendances of the
students at the courses, however, I don't want to use this as they
mightn't
turn up. I've then got to remove that instance from the table.

Dear Liz:

There is a way. It's not beautiful, but it will work.

To use this, the user must type in all the IDs without any mistake, and
place a comma (or any other separator you might choose) between the
numbers.
The user must not put any spaces in.

For the search, a where clause might be:

WHERE InStr("," & [Enter ID:] & ",", "," & ID & ",") > 0

If you want to search for 123, 134, and 145, the user types 123,134,145
in
the parameter. The search then looks at an id, say 234, like this:

InStr(",123,134,145,", ",234,")

All this funny stuff with the commas is necessary so it doesn't find 234
in
12345.

This is not pretty. But neither are your circumstances. Searching a
table
by a list of IDs sounds like a mess to me!

Tom Ellison

Can someone help. I need to retrieve peolple's addresses from a table.
The
only criteria I can use to identify these people is their ID number (an
auto
number field), there are no similarities i.e. their addresses aren't
the
same, and the numbers aren't consecutive.

Can I use a parameter query to specify the different ID numbers, I
might
hav
between 6 and 12 people that I need to retrieve the addresses for.

Thanks

Liz