Query is excluding needed information

[JerrodConaway]

I have created a query showing locations of a company's facilities, and product groups associated with that facility. Unfortunately the the products groups are located in one column and, if the facility produces more than one product, the product group is divided by a |, as shown below:

CABLE TRAY SECTION [05CT]|STEEL CONDUIT AND ELECTRICAL METALLIC TUBING SECTION [05RN]​

In my query, I only want to show the product code, which I resolved by creating another table and associating the code with the product group. However, this query will exclude those plants that produce multiple products. How can I show just the product codes for all the facilities, including those with multiple product groups?

In otherwords, I would like the above example to look like this: 05CT|05RN (or something similar to this).

 

[shudini]

is there any element of consistency, such as are there always brackets around the product codes and is one always before and after the | ?

if that is the case you can use a mixture of Mid() and Instr() functions to produce what you are looking for. For the data you provided, this

Expr1: Mid([product],InStr(1,[product],"[")+1,InStr(1,[product],"]")-InStr(1,[product],"[")-1) & "|" & Mid([product],InStr(InStr([product],"|"),[product],"[")+1,InStr(InStr([product],"|"),[product],"]")-InStr(InStr([product],"|"),[product],"[")-1)

would return your requested 05CT|05RN.

It looks a lot more complicated than it really is. Keep in mind that this will only work assuming your data follows this pattern consistenly. If not, it would be difficult to help without at least knowing all the possibilities.

 

[JerrodConaway]

Thank you for your response. The data is consistent, in that all the product codes appear in brackets, however if there is only one product then a | will not appear. Additionally, if the company did not provide a response, then --NOT Sure-- will appear.

 

[shudini]

Can there be more than 2 products (ie. more than 2 '|')?

What does that mean if the company doesn't respond it says --Not Sure--?

We can mix in a couple of iif() statements if we need to check for a limited number of conditions.

 

[JerrodConaway]

I apologize for the delay in responding to your question. I appreciate your assistance with this little project of mine. In terms of your question, every product section has brackets, such as CABLE TRAY SECTION [05CT], and if there are multiple product sections, they will be broken up by a |, as in the case of CABLE TRAY SECTION [05CT]|STEEL CONDUIT | ELECTRICAL METALLIC TUBING SECTION [05RN], however not every entry has the same number of product sections, as some have only one, and others have five. Additionally, if the individual did not enter a specific product section, "--NOT SURE--" will appear.
I attempted to use your Mid and InStr and combining it with OR, but after doing so, Access indicated that I used too many parenthies. Any suggestions?