Using MS Access/search query

Advertisement


Question
QUESTION: Hi, Can you please help me to make a multi line search query.
i have a list of account numbers for customers, however if i want to search only 10 account number, i can just copy paste the data in the text box, and it gives me result for the 10 account numbers.

ANSWER: You need to define what you are doing more clearly.

If you want to filter a query for a specific set of account numbers you can use a Multi-Select listbox to do it.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Ok i have a table named Accounts, with Felid1 as Account number and Feild2 Account name, there are more than 5000 line items in the table.
I want a form with a text box, where i can paste any number of account numbers, and  when i hit the search button, it should shows me the results of only accounts numbers in which i pasted in the text box.

eg. accts numbers   

1234
2123
2123
1232
2234

so if i paste the above list of accts numbers if should show only them.

please help me to make this query in ms acess

ANSWER: This is not a good way to do this. Pasting a set of accounts into a text box means you will have to parse out the various accounts into a criteria list. The better option is is use a multi-select listbox ans illustrated here:

http://access.mvps.org/access/forms/frm0007.htm

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thank you, however i am not so good in Ms acess, i just know the basics ,
multi select list box is not what i am looking for.
i want a search query , to search multiple lines pasted in a text box, from a table.

so if i want to search a list of items, i can just paste the data in search text box, and the search query look into the table and give us the result.

every item pasted in the search text box, will be on a new line.

Answer
I'm sorry, but I find this paradoxical. To paste a set of accounts into a text box and then use them as criteria for a query is actually harder than using a multi-select list box. It requires coding to parse out each account into a separate value that can be used.

If the list was a comma separated list, it would be easier and you could modify the code shown in that article to do it.

You say you are not so good in Access so why are you trying to do things the hardest way?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.