Multiple parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Tom Ellison said:
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 said:
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
Click to expand...
Click to expand...
 
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


Tom Ellison said:
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 said:
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
Click to expand...
Click to expand...
 
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
--

LizJ said:
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.

Tom Ellison said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top