Split text with length

[vivian2u]

Hi,
I have a textbox with multiline for the address such as:
18-G, Kuala Kangsar Road, 65890 Ipoh, Perak

I split it with "," so that each line can be saved to the add1, add2, add3, add4, add5, etc...respectively.
The result is:
18-G
Kuala Kangsar Road
65890 Ipoh
Perak

But I would like to split it with the fixed length, like:
18-G, Kuala Kangsar Road,
65890 Ipoh,
Perak

Please help in the VB6 coding. Hope my question is clear to you, thanks in advance.
Viv

[penagate]

No it isn't really

Can you show us exactly what your input is and what you want the output to be?

[vivian2u]

it is a texbox with multilines setting for user to key in the address.
I would like to split the address in the format like:
18-G, Kuala Kangsar Road,
65890 Ipoh,
Perak

and saved each line of the address to different field such as: add1, add2 and add3....

thanks

[penagate]

So each line goes into a different variable.

What result do you want?

[vivian2u]

i want to save each line to the different variable in the database.

so that it can be used for the report. I would like to display the address in seperated line and not a whole one line.

[penagate]

You want to access the database from VB? In that case, I can't help you, sorry.
Which bit of the coding do you need help with?

[olamm2k]

Your description of the problem is a little vague, but the following code should get you started.

You need to change "yourdatabase.mdb" to the name of your database. Inside the database, create a table with 6 fields - idnum as an autonumbered primary key and 5 fields named add1, add2, add3, add4 and add5. You need to create a form with a textbox called txtAddress and a commandbutton called cmdSave, then add a reference to the Microsoft ADO 2.x Library.

Option Explicit

Dim objConn As ADODB.Connection

Private Sub cmdSave_Click()
    Dim strAddressLines() As String
    Dim intPointer As Integer
    Dim objRS As ADODB.Recordset

    If txtAddress.Text <> "" Then
        strAddressLines = Split(txtAddress.Text, ",")
        
        Call ConnectToDatabase(App.Path & "\yourdatabase.mdb")
        
        Set objRS = New ADODB.Recordset
        objRS.Open "Addresses", objConn, adOpenStatic, _
          adLockPessimistic, adCmdTable
        objRS.AddNew
        
        For intPointer = 0 To UBound(strAddressLines)
            If intPointer < 5 Then
                objRS.Fields("add" & CStr(intPointer + 1)) = strAddressLines(intPointer)
            Else
                MsgBox "Too many address lines", vbOKOnly + vbExclamation, App.Title
                Exit For
            End If
        Next intPointer
        
        objRS.Update
        If objRS.State <> adStateClosed Then
            objRS.Close
        End If
        Set objRS = Nothing
        
        Call DisconnectFromDatabase
    End If
End Sub

Private Sub ConnectToDatabase(ByVal strPath As String)
    Set objConn = New ADODB.Connection
    
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strPath & ";" & _
      "Mode=Share Deny None;"
      
    objConn.Open
End Sub

Private Sub DisconnectFromDatabase()
    If objConn.State <> adStateClosed Then
        objConn.Close
    End If
    Set objConn = Nothing
End Sub 

I would recommend looking at some tutorials if you have never worked with databases before through VB.
eg. http://www.developerkb.com/modules/w...hp?category=23

[vivian2u]

Thank you for the coding.

I would like to split the text at "," , For example: 265, Kuala Kangsar Road, 65000 Ipoh. The result will be:

265
Kuala Kangsar Road
65000 Ipoh

I would like to split the text at "," but with the fixed length (30). The result
I would like to have is:

265, Kuala Kangsar Road
65000 Ipoh

Please help

[olamm2k]

That's more difficult to do - why not have separate text boxes for each line of the address? You'd find it a lot easier to code.

[MartinLiss]

Is this what you want?

Dim intPos As Integer
    Dim strTest As String
    Dim strAddress As String
    
    strAddress = "18-G, Kuala Kangsar Road, 65890 Ipoh, Perak"
    
    Do Until Len(strAddress) < 31
        strTest = Left$(strAddress, 30)
        intPos = InStrRev(strTest, ",")
        Debug.Print Left$(strAddress, intPos)
        strAddress = Right$(strAddress, Len(strAddress) - intPos)
        ' get rid of the leading space
        If Left$(strAddress, 1) = " " Then
            strAddress = Right$(strAddress, Len(strAddress) - 1)
        End If
    Loop
    ' Show the last part
    Debug.Print strAddress 

[olamm2k]

Just to point out, though - if a line is longer than 30 characters, it will go into an unending loop.

[MartinLiss]

OK, here is a fix.

Dim intPos As Integer
    Dim strTest As String
    Dim strAddress As String
    
    strAddress = "18-G, Kuala Kangsar Road, 65890 Ipoh, Perak ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc"
    
    Do Until Len(strAddress) < 31
        strTest = Left$(strAddress, 30)
        intPos = InStrRev(strTest, ",")
        If intPos = 0 Then
            'no comma so look for a space instead
            intPos = InStrRev(strTest, " ")
        End If
        If intPos = 0 Then
            ' no space either so just artificially break it
            intPos = 30
        End If
        Debug.Print Left$(strAddress, intPos)
        strAddress = Right$(strAddress, Len(strAddress) - intPos)
        ' get rid of the leading space
        If Left$(strAddress, 1) = " " Then
            strAddress = Right$(strAddress, Len(strAddress) - 1)
        End If
    Loop
    ' Show the last part
    Debug.Print strAddress 

[szlamany]

There should be no comma delimiter between a house (or lot#) and the street name.

Are the users in your locale used to entering: 123, MAIN STREET??

Is that comma needed?

[vivian2u]

yes, the user used to put delimiter between a house (or lot#) and the street name.

[MartinLiss]

Is my code anything like what you want?

[vivian2u]

Thank you. I need to try it out first. Heres are my coding, since i dont know how to fixed the length, i make it in other way. It might not in the good standard but it works. I will try yours too.

Dim strLineArr() As String
Dim strWordArr() As String
Dim intLineIdx As Integer, intWordIdx As Integer

strLineArr() = Split(txtAddress.Text, vbCrLf)

For intLineIdx = 0 To UBound(strLineArr)
strWordArr() = Split(strLineArr(intLineIdx), ",")
If Len(strWordArr(0)) < 5 Then
rs_add(1) = strWordArr(0) & "," & "" & strWordArr(1) & ","
For intWordIdx = 2 To UBound(strWordArr)
rs_add(intWordIdx) = strWordArr(intWordIdx) & ","
Next
Else
For intWordIdx = 0 To UBound(strWordArr)
rs_add(intWordIdx) = strWordArr(intWordIdx) & ","
Next
End If
Next

[szlamany]

Originally Posted by vivian2u

yes, the user used to put delimiter between a house (or lot#) and the street name.

Since the comma is required between the house/lot# and street name then you cannot use SPLIT - that's obvious.

Do you always have a house/lot# in your data entry - or can there be a street name by itself?