?
Solved

Split Array Access97/2000

Posted on 2003-03-18
5
Medium Priority
?
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

800 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