Re: Split text with length
No it isn't really :p
Can you show us exactly what your input is and what you want the output to be?
Re: Split text with length
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
Re: Split text with length
So each line goes into a different variable.
What result do you want?
Re: Split text with length
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.
Re: Split text with length
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?
Re: Split text with length
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.
VB Code:
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
Re: Split text with length
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 :ehh:
Re: Split text with length
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.
Re: Split text with length
Is this what you want?
VB Code:
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
Re: Split text with length
Just to point out, though - if a line is longer than 30 characters, it will go into an unending loop.
Re: Split text with length
OK, here is a fix.
VB Code:
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
Re: Split text with length
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?
Re: Split text with length
yes, the user used to put delimiter between a house (or lot#) and the street name.
Re: Split text with length
Is my code anything like what you want?
Re: Split text with length
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
Re: Split text with length
Quote:
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?