Prioritize substrings with a field

[Sephaerius]

Hi,
I've got a tricky query to run in which the data I'm given had a field which contains a number of error messages, sometimes single messages, and sometimes as many as 7 unique messages within a single string concatenated together.
I've got a priority list of each unique code. What I'm trying to do is boil down the 7 errors given into the "first" one that needs to be addressed.

I've got a table listing each unique error and have given each a numerical figure of priority.

So the data field looks like:

Error1 Error2 Error3 Error4

And my prioritized table looks like:

Error3 1
Error2 2


Error1 3
Error4 4


Something like that, right?

So what I would LIKE to do is run an update query against my data table, be able to identify the LOWEST priority value against whats within the field string. Here's my testing update query:

UPDATE Sheet1, x_dnb_ext_priority SET Sheet1.Priority = [x_dnb_ext_priority].[Priority]WHERE (((Sheet1.[Current DNB Extension]) Like "*" & [x_dnb_ext_priority].[Current DNB Extension] & "*"));

So this almost works, however, its returning the HIGHER value rather than the LOWER value that I'm after.

In the above example, it's giving me a "4" (= Error4) whereas I need a "1" (=Error3) because the lower number needs to be addressed first. How can I flip this?

[June7]

I think this is going to be more complicated. Updating the field might need VBA. Need a unique ID field in Sheet1 table. Then consider this query to prioritize:

SELECT Sheet1.ID, Sheet1.[Current DNB Extension], x_dnb_ext_priority.[Current DNB Extension], x_dnb_ext_priority.Priority
FROM x_dnb_ext_priority, Sheet1
WHERE (((IIf(InStr([Sheet1].[Current DNB Extension],[x_dnb_ext_priority].[Current DNB Extension]),True,False))=True))
ORDER BY Sheet1.ID, x_dnb_ext_priority.Priority;

[Sephaerius]

I think this is going to be more complicated. Updating the field might need VBA. Need a unique ID field in Sheet1 table. Then consider this query to prioritize:

SELECT Sheet1.ID, Sheet1.[Current DNB Extension], x_dnb_ext_priority.[Current DNB Extension], x_dnb_ext_priority.Priority
FROM x_dnb_ext_priority, Sheet1
WHERE (((IIf(InStr([Sheet1].[Current DNB Extension],[x_dnb_ext_priority].[Current DNB Extension]),True,False))=True))
ORDER BY Sheet1.ID, x_dnb_ext_priority.Priority;

This worked for my needs! Thanks!