Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Split Function (MSACCESS 2003)

    I am using a split function on a string that contains a forename and surname.

    Can someone please tell me what syntax to use in the code to prevent a subscript out of range error which happens when the string has only a single name (ie one word, not a pair with a space. Many thanks

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Split Function (MSACCESS 2003)

    Chech for the presence of a space first using Instr.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Function (MSACCESS 2003)

    Many thanks Patt. This fixed it

    Dim strValue

    Me.NN.Caption = Me.List1.Value

    strValue = Me.NN.Caption

    If InStr(strValue, " ") > 1 Then

    strAryWords = Split(strValue, " ")

    Me.NN1.Caption = strAryWords(0)
    Me.NN2.Caption = strAryWords(1)

    End If

    Best regards

  4. #4
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Function (MSACCESS 2003)

    One more problem. If I have a string, eg MyString, how can I tell how many spaces there are in it in order to run a split routine?

    Many thanks

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Function (MSACCESS 2003)

    to find the nuumber of " "s you can use:

    len(myString)-lenMystring(replace(mystring," ","")

    or if you want to reduce to only on space between names (I'm guessing that's your goal) you can use a "Trim"

    trAryWords = Split(trim(strValue), " ")

  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Function (MSACCESS 2003)

    Many thanks, however the syntax errors!!

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Function (MSACCESS 2003)

    Oops missed a ")"

    Len(MyString) - Len(Replace(MyString, " ", ""))

  8. #8
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Function (MSACCESS 2003)

    Thanks Mike, that woke it up. All the best

Posting Permissions

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