Text Replace

[tecknologika]

Weekly I receive an excel data set that I import into MS Access and generate reports. There is a text field in the data set containing information I need to search out and return values from an another table.

Example


There is a field titled FieldA in the data set that contains one or several condiments separated by the pipe symbol. I have a table tblCategory with three fields Condiment, Color, and Code. Is there a way in a query to list the corresponding color and code from tblCategory separated by comma, for the values in FieldA.

[CJ_London]

it can be done but probably very messy. you need to expand on your example - what is the maximum number of condiments that can be in FieldA and how many condiments in the categories table. Also, what is to happen if there are condiments in FieldA which are not in the other table (or misspelt)

suggest take a look at the concatrelated function here http://allenbrowne.com/func-concat.html which can probably be modified to meet your needs.

[tecknologika]

Thanks Ajax. To answer your questions: I've archived and analyzed the data for a little over a year and the most condiments I've seen in FieldA is four. There are 35 possibilities in the categories table. The list is sent to me from an external system from another entity and the end users has to select the condiments from a multi-select drop down. There will never be misspellings and I have a full list of possibilities (the 35 condiments in tblcategory). I actually want to, on the query level create two fields that will return 1 the code associated with the condiment in FieldA and 2 the category associated with the condiment in FieldA. I will take a look at the link you provided. If you can think of anything else that can help it would be appreciated.

[CJ_London]

the modification you will need to make to the concatrelated function is to pass fieldA as a parameter, then use the split function on the pipe char to an array. Then loop through the array for each array element to get the data from the categories table

It would be a lot easier if you could have the data on separate rows for each condiment - that query would look something like

SELECT FieldA, Condiment, Color, Code
FROM tblImport, tblCategories
WHERE FieldA Like "*" & Condiment & "*"

[rpeare]

tecknologika.zip

I tend to agree with Ajax. I would build an import function to split the individual components out then create a query with the end result. Here's a simple example using a spreadsheet and a button click import. From the end result it should be pretty easy to put together a query in the format you want.

[tecknologika]

Thanks rpeare!! This works.

[rpeare]

mark it solved!