Need help with trying to set criteria to pull data regardless of the position in the field

[Nuby]

I am trying to pull data (error codes) in Access, but they may not fall in the same position base on a pre-determined linked list. How do I set the criteria to pull the codes regardless of the position of the codes?; for example (CO:45, HE:MA63, CO:16, HE:MA81); (HE:MA81, CO:45, HE:MA63, CO:16)

[Bulzie]

Explain more on what you are trying to pull from that data above. Typically you would use Mid and Instr functions. Is that how the data resides in your field with the brackets, commas, semi-colons?

[Nuby]

No, the data does not contain brackets... that was just to show the example. My data is as displayed: CO:45, OA:97, HE:N45, HE:N111

[Bulzie]

So what do you want to pull, each value that is separate by a comma? Will they all have 2 letters then colon, then an alpha value? Is there always a space after the comma? Will there always be 4 values to pull per record or could that vary as well?

Also how do you want the output to look, separate records for each value?

[Nuby]

Yes, they all have alpha value in the format displayed above. Since the errors may fall in any order, I need to figure out how to set the criteria that no matter which position they fall, its counted.

[Bulzie]

For example to get first value could be:

value1 = left([Field], Instr(1, [Field], ",")-1)

Then use the Mid function to get the others using the position of the first Instr on the comma as the start of the next mid function.

[Bulzie]

You trying to do this in a query on VBA code? Also will there always be only 4 values but they could be in any order or could you have more then 4?

[Nuby]

So would I add that in the criteria? and how would the other values be accounted for?

[Nuby]

query. there could be more than 4 values.

[Bulzie]

So you want each value in a separate column in the query? Might be kind of tough given the variable number of values.

[Nuby]

No, the result will be in the same column... I usually have to manually/visually check to see if the errors are in another row in different order, then combine the totals for that error code: for example -CO:185, HE:N684 and HE:N684, CO:185 are the same error, they are just listed in different order. So what I would like for the query criteria to do is to check to see any claim exist with the errors regardless of the position of the error, as long as they match, count it as the same error. Instead of looking at them as they are different errors, return them as the same with a count of 2.

[Bulzie]

How many possible errors codes are there?

[Nuby]

about 103 combinations

[Nuby]

about 103 possible error code combinatation

[Micron]

No, the result will be in the same column...

I take that to mean CO:185, HE:N684 and HE:N684, CO:185 are in the same table field as in
CO:185
HE:N684
HE:N684
CO:185
A simple UNION query should take care of it then.
Not sure how you intend to count things. Take a look at http://www.fmsinc.com/microsoftacces...-all/index.htm