Spliting text in a text field, separated with "/" (1 Viewer)

[jcsuarez]

I need to separate text data like P0101 / P0102 onto 2 records P0101 and P0102. How can I do that? Is there any function in my ACCESS 2010 that may allow me to do that. Thanks a lot for your help

 

[arnelgp]

Public Function fnSplit(ByVal pString As String, ByVal pDelim As String, ByVal pItemNumber As Integer) As String
    Dim v As Variant
    If pItemNumber < 1 Then
        Exit Function
    End If
    v = Split(pString, pDelim)
    If UBound(v) + 1 >= pItemNumber Then
        fnSplit = Trim$(v(pItemNumber - 1))
    End If
End Function

to split your text:

fnSplit([fieldname], "/", 1)
fnSplit([fieldname], "/", 2)

 

[jcsuarez]

Thanks a lot, arnelgp. I will try this and let you know how it turns out

 

[Isaac]

Can do it right in the query

Expr1: left([ColumnName],instr(1,[ColumnName]," ")-1)
Expr2: replace([ColumnName],left([ColumnName],instr(1,[ColumnName]," ")-1) & " / ","")

 

[arnelgp]

Can do it right in the query

what if there is no "space"

Expr1: Trim$(left$([ColumnName],instr(1,[ColumnName],"/")-1))
Expr2: Trim$(Mid$([ColumnName], Instr(1,[ColumnName], "/")+1))

 

[Isaac]

what if there is no "space"

Then I guess the original post wouldn't be accurate, which states that there is a space.

 

[arnelgp]

original post wouldn't be accurate

for all we know, data can be:

P0101 / P0102
P0101/P0102
P0101 / P0102
P0101/ P0102

 

[jcsuarez]

Tank ypu, both, arnelgp and Isaac. I´ll tried all the ways you told me, but I guess I need more help, since my knowledge of Vbasic or ACCESS is not very strong. Here is the thing: I have a person filling data on a form, in a control named "partan". I need to split the text he enters whenever he puts a "/", into to controls named "pa2" and "pa3". I put the instructions you sent me as an event procedure, but didn´t work for me. Would you be so kind as to help me a little further? Thanks a lot

 

[Isaac]

for all we know

Or, you could just take the OP's word for it, since their original example included a space.

Why go out of your way to make up something you haven't been told by the OP?
In that case, I wonder if the value might be: @#$%^@#$%@$#5/spaghetti ? It's possible, right?

 

[arnelgp]

now on your form, add code to AfterUpdate event of "partan" control:

private sub partan_afterUpdate()
If Me!partan & "" Like "'*/*'" Then
me!pa2 = Trim$(Left$(Me!partan, Instr(1, Me!partan, "/") -1))
me!pa3 = Trim$(Mid$(Me!partan, Instr(1, Me!partan, "/") + 1))
end if
end sub

@#$%^@#$%@$#5/spaghetti ? It's possible, right?

that is possible, don't you know?

 

[jcsuarez]

Thanks, arnelgp! In fact, my data don´t have a space, it´like P0101/P0102.
But I don´t know what am I doing wrong, ´cause I cannot make it work: I´m attaching a very small database, with just a Table and a Form with the procedure. Perhaps you could tell me what error I´m making. Thanks for that
And another question: How would Y have to change it if I have 3 or 4 "/" in the field?

 

[Uncle Gizmo]

That's not a database, that's a spreadsheet! If you carry on the way you're going you're heading for a world of hurt! Please read my blog here:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

Which will give you some idea of the issues you will face if you continue with that structure.
I suggest you seek some advice from members of this forum, to help you construct your database in the correct manner.
The first step would be to explain what you want your database to do. I'm sure someone will advise you on a different approach.

 

[jcsuarez]

Uncle Gizmo: I find your comments absolutely unhelpful and rather insulting. I had the impulse of getting off the Forum forever. Is that what you want?

 

[jcsuarez]

arnelgp: Thanks a lot for your help! Your messages allowed me to solve my problem, though I could not make the If command work

 

[MajP]

You modified what @arnelgp wrote. You changed all the Me (current form) to the word Partan. This works for me.

Private Sub partan_AfterUpdate()
    If InStr(Me.partan, "/") > 0 Then
     Me.pa2 = Trim$(Left$(Me.partan, InStr(1, Me.partan, "/") - 1))
     Me.pa3 = Trim$(Mid$(Me.partan, InStr(1, Me.partan, "/") + 1))
    End If
End Sub

 

[Uncle Gizmo]

Uncle Gizmo: I find your comments absolutely unhelpful and rather insulting. I had the impulse of getting off the Forum forever. Is that what you want?

Actually I completely disagree with your summarisation of what I have said to you. It is obvious you have replied without looking at the information I provided or you might have come back with a different attitude!

 

[Uncle Gizmo]

Uncle Gizmo: I find your comments absolutely unhelpful and rather insulting. I had the impulse of getting off the Forum forever. Is that what you want?

The video below demonstrates a method of solving this problem much more in line with the the recommended way of using MS Access. If anyone would like a copy of the sample database used in the Video, then please contact me and I will explain how you can get a copy.

Split CSV in to Rows - Nifty Access​

 

[The_Doc_Man]

@jcsuarez - Please understand that when you come to an Access forum and ask a question in an Access topic, you are going to get an Access answer. If your design is inefficient or structurally just wrong with respect to Access guidelines, we are going to give you advice that will help you long-term. And that is what Uncle Gizmo did - he gave you long-term advice.

Understand also that we have seen the mistakes people make. (In my case, I've probably made at least 80% of all possible Access mistakes, so I guess that makes me almost an expert?) We offer you solutions, but also offer you guidelines on how to avoid awkward or long-term unsupportable designs. It is unfortunate that you might have such a design, but it is the purpose of this forum to help people better use Access for their personal and business needs. If your feathers got ruffled because your project was judged to contain design flaws, you can either be incensed by the comments or learn from them. Why ask for advice if you are going to ignore the advice you get?

If you are so upset by UG's response that you want to look elsewhere for help, we can't stop you. Nor would we. But you should be aware that you will probably get similar responses from other Access-oriented forums. First, because many of our members are also members of those other forums. Second, because regardless of what you think of us, we have one of the higher ratings for Access help. Not all of us are experts in everything, but Uncle Gizmo is one of our more knowledgeable members when it comes to migrating from Excel designs to Access designs.

 

[Pat Hartman]

@jcsuarez
My recommendation is to ask UG for a copy of the example he built for you. It probably isn't exactly what you need since it forces you to use a form to control the outer loop, but it will give you insight into the technique you need to use (a DAO loop to read rows from one table and an inner DAO loop to add rows to a second table).

Just FYI.
A query cannot create records out of thin air even with code in a function unless that function runs an append query or a DAO add loop. Therefore, the most efficient method is to open a recordset and as you read each record split the string using the split function and use an inner loop to insert a row in a second table for each item in the array created by the Split() function.

Except for UG and Doc, the other people who replied concentrated on the Split() but didn't tell you how to get the rows into a table. Hopefully, you will convert the smushed data into a properly normalized schema and move on with your design. Do NOT fall into the trap of "we only have two". Whenever you have more than one, you have "many" and "many" requires a second table to implement correctly. So do NOT just use the other suggestions to add data to two columns.