Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting data across fields (Access 97 SR-2)

    I have 2 field names, 'SchoolName' and 'AddressLine1' - my problem is that where SchoolName is >30 characters, I need to be able to split the remaining data over to AddressLine1 but to the nearest word and SchoolName must be no bigger than 30 characters.
    I've managed to split any data over 30 characters over to AddressLine1 (by using the left and mid commands) but I'm not sure how to go about searching for the nearest space before reaching 30 characters in the SchoolName field, and splitting it from there???

    Example:

    SchoolName = John Smith Nursery & Primary School

    After splitting it:

    SchoolName = John Smith Nursery & Primary S
    AddressLine1 = chool

    But I want it to look like:

    SchoolName = John Smith Nursery & Primary
    AddressLine1 = School

    I hope that makes sense, any help much appreciated, thanks.

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting data across fields (Access 97 SR-2)

    Here is one way to do it:

    <pre>Dim strSchool30 as string
    Dim intLoop as Integer

    strSchool30 = left$(strFullSchoolName, 30)

    Do While (instr(intLoop+1 ,strSchool30," ") <> 0)
    intLoop = instr(intLoop+1 ,strSchool30," ")
    Loop

    If intLoop = 0 then
    msgbox "No place to split school name cleanly", vbOKOnly
    Else
    strSchool30 = Left$(strFullSchool, intLoop)
    Address1 = mid(strFullSchool, intLoop +1)
    End If</pre>


    HTH,
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting data across fields (Access 97 SR-2)

    Thanks, will try that out.

    *** Update ***

    Thanks again - that really helped me out - here's the final code that I produced from it and it works a treat!

    <pre>Dim db As Database, rec As Recordset
    Dim strSchool As String, strAddress1 As String, strSchool30 As String, intLoop As Integer

    Set db = CurrentDb()
    Set rec = db.OpenRecordset("tblJemMainTEMP")

    Do Until rec.EOF
    If Len(rec("School")) > 30 Then
    intLoop = 0
    strSchool = rec("School")
    strSchool30 = Left$(strSchool, 30)

    Do While (InStr(intLoop + 1, strSchool30, " ") <> 0)
    intLoop = InStr(intLoop + 1, strSchool30, " ")
    Loop

    If intLoop = 0 Then
    rec.MoveNext
    Else
    rec.Edit
    strSchool30 = Left$(strSchool, intLoop)
    strAddress1 = Mid(strSchool, intLoop + 1)
    rec("ADD3") = rec("ADD2")
    rec("ADD2") = rec("SCH2/ADD1")
    rec("SCH2/ADD1") = strAddress1
    rec("SCHOOL") = strSchool30
    rec.Update
    rec.MoveNext
    End If
    Else
    rec.MoveNext
    End If
    Loop</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •