"Unconcatenating" Data In A Field

[EzGoingKev]

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.

 

[theDBguy]

I'd say give it a try and let us know how it goes.

 

[EzGoingKev]

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.

 

[theDBguy]

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]

Sorry I had a lot going on. My niece wrote a VBA for me that took care of it.

Thanks for the help.

 

[isladogs]

It might help others in the future if you posted the code your niece kindly wrote for you

 

[theDBguy]

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]

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.

 

[theDBguy]

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]

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 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]

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 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]

Here is some test data:

f1	f2
test1	123,456
test2	789
test3	987;654;321
test4
test5	quick/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]

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:

var=Split(Replace(TextIn,";",","),",")

Hope it helps...

 

[EzGoingKev]

That works. Thanks.

 

[theDBguy]

That works. Thanks.

You're welcome. Good luck with your project.

 

[CJ_London]

One way

1. copy and paste these two functions into a standard module

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

3. copy this sql to a new query

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