Create a query for specified text in a field
This is a tough problem to solve for more than one customer at a time due to
the combination of multiple customers in one field.
For a SINGLE customer
Parameters [Which Customer] Text;
SELECT [Which Customer] as TheCustomer
, Document
, Title
FROM [SomeTable]
WHERE Customer LIKE "*" & [Which Customer] & "*"
IF you need to do this for multiple customers at once and cannot change the
structure of your source table, your best solution would be to create a table
of the unique customer values and use that in a non-equi join.
SELECT [CustomerList].Customer
, [SomeTable].Document
, [SomeTable].Title
FROM [SomeTable] INNER JOIN [CustomerList]
On [SomeTable].Customer LIKE "*" & [CustomerList].Customer & "*"
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Jacqueline _Neo wrote:
Hi there,
I have a number of fields in my query. They are Customer, Document, Title.
In this query, i have include a input dialog for the user to enter the
customer name.
In the Customer field, there are several customers in one record that uses
the same document.
Example,
Customer | Document | Title
ABC/3H/SST | 12346 | This is a test procedure
ABC/3H | 11234 | Procedure
I would like the output shows a list of the documents used for the specified
customer. Example,
I want to know the list of documents that is for Customer: ABC so the output
will be
Customer | Document | Title
ABC | 12346 | This is a test procedure
ABC | 11234 | Procedure
For Customer : SST
Customer | Document | Title
SST | 12346 | This is a test procedure
How do I go about creating this scenario for this case? I tried to set the
criteria : Like "SST*" or Like "ABC*" under the Customer field but the output
shows everything and did not do a filter.
Pls help.
Thanks!
|