"Unconcatenating" Data In A Field (1 Viewer)

EzGoingKev

Registered User.
Local time
Today, 05:26
Joined
Nov 8, 2019
Messages
178
DBGuy -

Thanks for sending that over.

The funny thing is that after I posted the sample data up I thought about taking the other data set I was going to link to, combining the coding with the backward slash in the middle and doing what you did in your query.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:26
Joined
Oct 29, 2018
Messages
21,357
I'd say give it a try and let us know how it goes.
 

EzGoingKev

Registered User.
Local time
Today, 05:26
Joined
Nov 8, 2019
Messages
178
My way does not work.

One model code is paired with 73/23475. Using the Like "*" & [FieldName] & "*" it comes back with both 73\23475 and 3\23.

DBGuy, I just added the those codes to the tables and ran the query. Yours does the same as mine.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:26
Joined
Oct 29, 2018
Messages
21,357
My way does not work.

One model code is paired with 73/23475. Using the Like "*" & [FieldName] & "*" it comes back with both 73\23475 and 3\23.

DBGuy, I just added the those codes to the tables and ran the query. Yours does the same as mine.
Hi. That's why it's important we get to see a good set of sample data. Can you repost the db with the new set of data that is not working, so I can check why it doesn't work? Before you do though, please make a table with the result you're getting, so I could have something to compare with. Thanks.
 

EzGoingKev

Registered User.
Local time
Today, 05:26
Joined
Nov 8, 2019
Messages
178
Sorry I had a lot going on. My niece wrote a VBA for me that took care of it.

Thanks for the help.
 

isladogs

MVP / VIP
Local time
Today, 09:26
Joined
Jan 14, 2017
Messages
18,186
It might help others in the future if you posted the code your niece kindly wrote for you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:26
Joined
Oct 29, 2018
Messages
21,357
Sorry I had a lot going on. My niece wrote a VBA for me that took care of it.

Thanks for the help.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

EzGoingKev

Registered User.
Local time
Today, 05:26
Joined
Nov 8, 2019
Messages
178
See attached.

The MDL_MAST table is the data I had to work with.

The Delimiters query counts how many semi-colons there are in the field in the ENH_MODEL field in the MDL_MAST table.

Table1 is numbers table that is used for the ParseText function.

If you run the delimiters query the highest amount of semi-colons is (29). I did Table1 to 30 to be safe.

Look at the query to see how it works.
 

Attachments

  • data_parse.zip
    383.5 KB · Views: 104
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:26
Joined
Oct 29, 2018
Messages
21,357
See attached.

The MDL_MAST table is the data I had to work with.

The Delimiters query counts how many semi-colons there are in the field in the ENH_MODEL field in the MDL_MAST table.

Table1 is numbers table that is used for the ParseText function.

If you run the delimiters query the highest amount of semi-colons is (29). I did Table1 to 30 to be safe.

Look at the query to see how it works.
Hi. Thanks for sharing. It seems to be missing an object called "MDL_MAST_2" referenced in the "result_needed_2" query.
 

EzGoingKev

Registered User.
Local time
Today, 05:26
Joined
Nov 8, 2019
Messages
178
Hi. Thanks for sharing. It seems to be missing an object called "MDL_MAST_2" referenced in the "result_needed_2" query.
I thought it was fixed. If delete the MDL_MAST_2 table from the query it will run.

I fixed it and replaced the zip folder.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:26
Joined
Oct 29, 2018
Messages
21,357
I thought it was fixed. If delete the MDL_MAST_2 table from the query it will run.

I fixed it and replaced the zip folder.
Yes it does run. Thank you for the additional information. Cheers!
 

EzGoingKev

Registered User.
Local time
Today, 05:26
Joined
Nov 8, 2019
Messages
178
I have a question on this.

In ParseText module the code reads:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ";", -1)
ParseText = var(X)


If ParseText = "" Then
ParseText = "empty"
End If

End Function

I want it to do it where there is more than a comma. I tried:

var = Split(TextIn, In (",", ";", "/"), -1)
ParseText = var(X)

And right off the bat it did not like the In and the commas used as separators.

I also tried:

var = Split(TextIn, "," Or ";" Or "/", -1)
ParseText = var(X)

That runs but does not perform the Parse Text function.

I tried this:

var = Split(TextIn, ",", -1)
ParseText = var(X)
var = Split(TextIn, ";", -1)
ParseText = var(X)
var = Split(TextIn, "/", -1)
ParseText = var(X)

It runs but it only parses the text where the last variable is.

What is the correct way to modify the code?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:26
Joined
Oct 29, 2018
Messages
21,357
I want it to do it where there is more than a comma.
Hi. Sorry, I don't understand what you mean by that. Can you show us an example of the data where there is "more than a comma?" Thanks.
 

EzGoingKev

Registered User.
Local time
Today, 05:26
Joined
Nov 8, 2019
Messages
178
Here is some test data:

f1f2
test1123,456
test2789
test3987;654;321
test4
test5quick/brown/fox

I do not think I would run into multiple separators in one data set but I would like to have a universal query set up where I can copy/paste data into a table like the above and have it parse the data whether it had a comma, semicolon, etc.

Currently the code is written with a comma as the criteria. I want it to use multiple characters as criteria.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:26
Joined
Oct 29, 2018
Messages
21,357
Hi. Thanks for the clarification. Since the Split() function can only use one delimiter, you will have to convert all other delimiters to match. For example, if you have both comma and semicolor in the data and you want to use the Split() function, you could try it this way:
Code:
var=Split(Replace(TextIn,";",","),",")
Hope it helps...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:26
Joined
Feb 19, 2013
Messages
16,553
One way

1. copy and paste these two functions into a standard module
Code:
Option Compare Database
Option Explicit

Function Elements(S As String) As Long

    Elements = UBound(Split(S, ";"))
    
End Function

Function Element(S As String, N As Long) As String

    Element = Split(S, ";")(N - 1)
    
End Function
2. Create a table called tblRows that looks like this - add enough numbers for your maximum number of models
Capture.PNG

3. copy this sql to a new query
Code:
SELECT [1_sample_data].MODEL_CODE, Left(element([ENH_MODEL],[num]),2) AS MakeCode, Mid(element([ENH_MODEL],[num]),4) AS Model
FROM 1_sample_data, tblRows
WHERE (((tblRows.num)<=elements([enh_Model])));

which produces the result you require
 

Users who are viewing this thread

Top Bottom