Parsing data from field in microsoft access table

I have a few rows of data in a microsoft access table similar to the attached.

The fields Title and ItemNum are my current two fields. I need a query that will update the DesiredOutputField with the values as seen in the attached database.

The goal is to parse out any data from the beginning of the Title field that matches the itemnum field.

If nothing matches, then leave blank.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
The issue with your data is that "matching" the first few characters is difficult because the rules are different for each.
Some fields have spaces, others have dashes. some are a continuous string of characters.

Obviously the ultimate goal here is to consider storing this data in Separate fields...
Instead of going through all the gesticulations of trying to pares the data (and *Pray* that the value was entered correctly)
gleveratorAuthor Commented:
what do you mean by storing this data in separate fields?
Jeffrey CoachmanMIS LiasonCommented:
Instead of having free form text fields, have one field for the "Prefix".

So this is a chicken and egg question...

Should the prefixes be created first (so it can be pulled from a table and be consistient)
Or is the "prefix" pulled from the free text field?
(Because if you want the Prefix to be pulled from the free text fields, then you will have to *Make Sure" that there is a distinct, consistent deliminator between the free text and the Prefix.

So it would be helpful if you first told us a bit more about these fields and how they are used and entered.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gleveratorAuthor Commented:
Hi Jeff, still not exactly sure what you mean.

can you provide an example or two of the 2 different methods you discussed and output that would occur?
Jeffrey CoachmanMIS LiasonCommented:
Based on your sample file, it looks like you should be first selecting the "Prefix (in one field), then typing in the free text. (in another field)

But again, you still have not told be the details of this system.
Because, as you can see here, your current system makes it extremely difficult to separate out the "Prefix".

If you first selected the "Prefix" (from a table) then entered the free text you would have one field for the prefix and another filed for the Free text.

You could then concatenate the two fields together for display purposes.

This is a more "Normalized" approach.
Instead of trying to use all sorts of custom code to "parse" all the possible ways a use can enter the entire string and "pull out" the prefix...

Make sense?

Jeffrey CoachmanMIS LiasonCommented:
Control1, on a form, a combobox, listing values form a "Prefix" table.
You select a Prefix:
ex: 1234
Now 1234 is in the Prefix field.

Then in the "Free text" fields (Title and ItemNum), you type in all the other free text:

Now you have a value in the Free text field(s) and a value in the "Prefix" field.
So far so good?

If there is no "Prefix" selected, then it will be blank.
So, (If I am understanding your design here), the "Prefix" field will be what you are calling "DesiredOutputField"

Now you can concatenate the prefix to the Free text  Fields (in a query or in a control), and display it anyway/anywhere you like.

gleveratorAuthor Commented:
Hi Jeff, i think you are misunderstand the use here.

I have a table with about 6,000 rows of data with the title and itemnum field.

This is not for a front end user.

I need to parse out the desiredoutputfield for database purposes.

I understand that it is not easy as some have spaces, some have -, and some are just a string, however i need to get this done.

Can you think of any way i can do this thats NOT manual?
Jeffrey CoachmanMIS LiasonCommented:
Well without the "delineation" being consistent, this will be difficult.
...Not impossible, ...because it looks like you need all the matching/leading characters to be inserted into the DesiredOutput field....
So I will contact another Expert and see if he is interested...

Or else *you* must tell us how to delineate the fields to get "Most" of the DesiredOtput fields filled in.
The rest you will have to do manually.

Either that or give us a "Blanket" criteria (ex.: Pull the first 15 characters from both fields and populate the DesiredOutput filed, ...then you will have to delete what you don't need)

But I'll see if the other expert will have a shot at this as is...

Patrick MatthewsCommented:
It looks to me like you have 2 strings, and you want to keep returning characters from the start of the string as long as the characters from the two strings are matching.  That being the case, I added this function to a regular VBA module:

Function MatchFromStart(String1 As Variant, String2 As Variant) As Variant
    Dim Counter As Long
    Dim Result As String
    If IsNull(String1) Or IsNull(String2) Then
        MatchFromStart = Null
        For Counter = 1 To Len(String1)
            If Mid(String1, Counter, 1) = Mid(String2, Counter, 1) Then
                Result = Result & Mid(String1, Counter, 1)
                Exit For
            End If
        MatchFromStart = Result
    End If
End Function

Open in new window

Now, if I run this query, the output of my function appears to match your desired output:

SELECT Title, ItemNum, DesiredOutputField, MatchFromStart([Title], [ItemNum]) AS Expr1
FROM Table1;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gleveratorAuthor Commented:
ok thanks Jeff, would prefer to use as is.

If he cant do it, let me know and i will try to assist further although im not sure exactly how i can help.
gleveratorAuthor Commented:
Wow, great work, thanks.
Jeffrey CoachmanMIS LiasonCommented:
Hey, I'm glad that matthewspatrick was able to step in and get you sorted.

The goal is always to get you the solution you need, that end, if I can't do it, then I will try to find someone who can.

I had a feeling that matthewspatrick's expertise in "Text manipulation" would get you what you needed.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.