[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Split Array Access97/2000

Posted on 2003-03-18
5
Medium Priority
?
448 Views
Last Modified: 2012-05-04
I have a Split command in my database that Uses the Split function in VBA to split a String into an Array, using the Carriage Return (Chr(13)) as a delimiter (See the code given below). This works absolutely fine in Access 2000. However, the people I'm designing this DB for are still running Access97! The Split Function is not defined in Access97! Does anyone know a peice of Code that would give me the same result in '97 as using Split in p to date versions?

All help very much appreciated. Cheers.

The full code for my function is:


Forms!Customers!PastedText.SetFocus
DoCmd.RunCommand acCmdPaste
Forms!Customers!Address1.SetFocus

Dim PastedText As String
Dim Address1 As String, Address2 As String, Address3 As String, Address4 As String, _
    Address5 As String
Dim MyChar
MyChar = Chr(13)

PastedText = Forms!Customers!PastedText

arrPastedText = Split(PastedText, MyChar)
Dim MyLength
MyCount = UBound(arrPastedText)
If MyCount = 0 Then
   Address1 = arrPastedText(0)
   Forms!Customers!Address1 = Address1
   Forms!Customers!AccountName.SetFocus

   ElseIf MyCount = 1 Then
   Address1 = arrPastedText(0)
   Forms!Customers!Address1 = Address1
   Address2 = arrPastedText(1)
   MyLength = Len(Address2) - 1
   Address2 = Right(Address2, MyLength)
   Forms!Customers!PostCode = Address2
   Forms!Customers!AccountName.SetFocus

   ElseIf MyCount = 2 Then
   Address1 = arrPastedText(0)
   Forms!Customers!Address1 = Address1
   Address2 = arrPastedText(1)
   MyLength = Len(Address2) - 1
   Address2 = Right(Address2, MyLength)
   Forms!Customers!Address2 = Address2
   Address3 = arrPastedText(2)
   MyLength = Len(Address3) - 1
   Address3 = Right(Address3, MyLength)
   Forms!Customers!PostCode = Address3
   Forms!Customers!AccountName.SetFocus

   ElseIf MyCount = 3 Then
   Address1 = arrPastedText(0)
   Forms!Customers!Address1 = Address1
   Address2 = arrPastedText(1)
   MyLength = Len(Address2) - 1
   Address2 = Right(Address2, MyLength)
   Forms!Customers!Address2 = Address2
   Address3 = arrPastedText(2)
   MyLength = Len(Address3) - 1
   Address3 = Right(Address3, MyLength)
   Forms!Customers!Address3 = Address3
   Address4 = arrPastedText(3)
   MyLength = Len(Address4) - 1
   Address4 = Right(Address4, MyLength)
   Forms!Customers!PostCode = Address4
   Forms!Customers!AccountName.SetFocus

   ElseIf MyCount >= 4 Then
   Address1 = arrPastedText(0)
   Forms!Customers!Address1 = Address1
   Address2 = arrPastedText(1)
   MyLength = Len(Address2) - 1
   Address2 = Right(Address2, MyLength)
   Forms!Customers!Address2 = Address2
   Address3 = arrPastedText(2)
   MyLength = Len(Address3) - 1
   Address3 = Right(Address3, MyLength)
   Forms!Customers!Address3 = Address3
   Address4 = arrPastedText(3)
   MyLength = Len(Address4) - 1
   Address4 = Right(Address4, MyLength)
   Forms!Customers!Address4 = Address4
   Address5 = arrPastedText(4)
   MyLength = Len(Address5) - 1
   Address5 = Right(Address5, MyLength)
   Forms!Customers!PostCode = Address5
   Forms!Customers!AccountName.SetFocus
0
Comment
Question by:nharbour
4 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 200 total points
ID: 8160315
You'll have to use the instr() function to determine where the space is and manullay insert the text into the array.....


Brett
0
 
LVL 15

Accepted Solution

by:
cquinn earned 400 total points
ID: 8160653
The following code does something similar, splitting a name at each character you specify - you can tweak it to address your problem

Public Function ConvName(sName As String, sSplitChar as String) As String()
Dim iSpace As Integer, iLoop As Integer
Dim saNames() As String, n As Integer
iLoop = 0
ReDim Preserve saNames(0)
sName= Trim$(sName)
Do While InStr(sName, sSplitChar) > 0
    iSpace = InStr(sName, sSplitChar)
    ReDim Preserve saNames(UBound(saNames) + 1)
    saNames(iLoop) = Trim$(Mid$(sName, 1, iSpace))
    sName = Trim$(Mid$(sName, iSpace))
    iLoop = iLoop + 1
Loop
saNames(iLoop) = sName

ConvName=saNames()
End Function
0
 
LVL 1

Assisted Solution

by:rveliz
rveliz earned 400 total points
ID: 8162374
Try this.  I had a problem assigning arrays to another array, so I included the array as a parameter.  Since it is passed by reference all the changes are reflected properly.  Hope it helps.



Public Function Split(ByVal strString As String _
                    , strDelimiter As String _
                    , strArray() As String _
                    , Optional intCompare As Long _
                            = vbBinaryCompare) As Variant
                           
On Error GoTo Err_Split
                   
    Dim intArrayUpper As Integer _
        , intPosition As Integer
       
    intArrayUpper = 0
    intPosition = InStr(1, strString, strDelimiter, intCompare)
   
    Do While intPosition > 0
        ReDim Preserve strArray(intArrayUpper)
        strArray(intArrayUpper) = Left$(strString, intPosition - 1)
        strString = Right$(strString, Len(strString) - intPosition)
        intPosition = InStr(1, strString, strDelimiter, intCompare)
        intArrayUpper = intArrayUpper + 1
    Loop
   
    ReDim Preserve strArray(intArrayUpper)
    strArray(intArrayUpper) = strString
    Split = strArray
   
Exit_Split:
    Exit Function
   
Err_Split:
    msgbox Err.Description
    Resume Exit_Split
   
End Function
0
 
LVL 2

Expert Comment

by:HobsonT
ID: 8715003
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area to:
Split points between arbert (50), cquinn (100), rveliz (100)
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Trevor
EE Cleanup Volunteer
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question