If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple parameters
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 |
#2
|
|||
|
|||
Multiple parameters
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" wrote in message ... 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 |
#3
|
|||
|
|||
Multiple parameters
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" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
Multiple parameters
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" wrote in message ... 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" wrote in message ... 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 |
#5
|
|||
|
|||
Multiple parameters
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" wrote in message news 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" wrote: 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a form w/ multiple parameters to retrieve data only | DGregg | New Users | 0 | June 28th, 2005 12:11 AM |
Creating a form using multiple parameters | DGregg | Using Forms | 1 | June 22nd, 2005 01:56 AM |
Help! Setting multiple parameters on a form to execute query | DGregg | General Discussion | 0 | June 17th, 2005 01:45 AM |
Multiple merge with IncludeText generating extraneous hidden c | Judy | Mailmerge | 2 | May 5th, 2005 03:51 PM |
Multiple docs open multiple words | Ron Smith | New Users | 2 | December 11th, 2004 09:21 PM |