• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

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.

Thanks
Database1.accdb
0
gleverator
Asked:
gleverator
  • 6
  • 5
1 Solution
 
Jeffrey CoachmanCommented:
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)
0
 
gleveratorAuthor Commented:
what do you mean by storing this data in separate fields?
0
 
Jeffrey CoachmanCommented:
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.

JeffCoachman
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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?
0
 
Jeffrey CoachmanCommented:
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?

JeffCoachman
0
 
Jeffrey CoachmanCommented:
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.

JeffCoachman
0
 
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?
0
 
Jeffrey CoachmanCommented:
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...

Jeff
0
 
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
    Else
        For Counter = 1 To Len(String1)
            If Mid(String1, Counter, 1) = Mid(String2, Counter, 1) Then
                Result = Result & Mid(String1, Counter, 1)
            Else
                Exit For
            End If
        Next
        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

0
 
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.
0
 
gleveratorAuthor Commented:
Wow, great work, thanks.
0
 
Jeffrey CoachmanCommented:
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, ...to 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.

;-)

Jeff
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now