Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: extract characters from a text field access 2007

    I am looking to extract certain characters from a text field (these are order numbers) and create 2 columns with them
    These are imported and have a minimum 8 characters to 12 characters. 12 is the max.

    They can have variances such as:
    34027805
    34027847 F
    34027971-01
    34028182-02R
    34021078X

    I need to create 2 field in a query of:
    1.) the 5 middle characters;
    NENO:
    27805
    27847
    27971
    28182
    21078

    2.) any of the right characters after 8th (I do not need the hyphen or spaces)
    BO:
    (allow null)
    F
    01
    02R
    X

    I would love to write a public function to add 2 fields to a query, or 2 appropriate query fields to do this

    I tested in a query and had success for my NENO with either : NENO1: Replace([NENO],"340","") or NENO2: Left([NENO1],5)
    but I can't grasp all the variances of BO and think VBA is needed to run if's?

    can anyone provide examples of how to accomplish this?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends where you are using this, and how it is to be called, but you could write a function that returns the separate values


    if the function was to be used by a form or report, then a single function returning an array of values would work
    if its to be used in a query then the following may work

    Code:
    'this function strips out data from the supplied token
    'token must be at least 8 characters long (if less than 8 then spaces will be returned. you may need to rethink that if you could get data that breaks this rule
    'the function returns characters 4 to 8 if GetSuffix is false
    'the function returns characters 9 onwards if GetSuffix is true AND the 9th character is not a space or hyphen, otherwise it returns characters 10 onwards
    'if getsuffix is true and there are only 8 characters the function will return an empty string
    public function SplitToken(Token as string, optional GetSuffix as Boolean  = False) as string
      SplitToken = "" 'set up our default return value
      'ok so lets validate the data. the token must be at least 8 characters long
      if len(token) >= 8 then 
        if GetSuffix = true  then then 'we want any trailing data after the 8th character excluding hyphens or spaces
          if len(token) > 8 then 'only do this if the token is more than 8 characters long, otherwise return an empty string
            SplitToken = mid$(Token, 9)
            'check to see if the new splitToken is prefixed by " " or "-"
            select case chr$(left(SplitToken,1)) 'strictly speaking you could do this a  simple if then else, or if a or b
            'doing it this way allows for the list of 'unapproved' symbols to be expanded and cater for special cases (say if the data included !$ that needed to be chopped)
              case is 32: 'its a space
                SplitToken = mid$(splittoken,2) 'chop off the leading character
              case is 45: 'its a hyphen
                SplitToken = mid$(splittoken,2) 'chop off the leading character
             'case else 'do nothing
             end select
          endif
        else 'we want character 4 to 8
           SplitToken = mid$(Token, 4,5)
        endif
      endif
    end function
    according to this the ASCII representation of space is 32 and the hyphen is 45

    then call the function in a query in this style (replace 'MyValue', 'MyTable' along with 'my', 'column', 'list' with the correct names of your table and columns)
    select my, column, list, SplitToken(MyValue,False) as FirstElement, SplitToken(MyValue,True) as SecondElement from MyTable

    if you know you are only ever going to deal with data with a - or space that needs chopping then
    Code:
    'this function strips out data from the supplied token
    'token must be at least 8 characters long (if less than 8 then spaces will be returned. you may need to rethink that if you could get data that breaks this rule
    'the function returns characters 4 to 8 if GetSuffix is false
    'the function returns characters 9 onwards if GetSuffix is true AND the 9th character is not a space or hyphen, otherwise it returns characters 10 onwards
    'if getsuffix is true and there are only 8 characters the function will return an empty string
    public function SplitToken(Token as string, optional GetSuffix as Boolean  = False) as string
    
      SplitToken = "" 'set up our default return value
      dim ASCIIValue as integer 
      'ok so lets validate the data. the token must be at least 8 characters long
      if len(token) >= 8 then 
        if GetSuffix = true  then then 'we want any trailing data after the 8th character excluding hyphens or spaces
          if len(token) > 8 then 'only do this if the token is more than 8 characters long, otherwise return an empty string
            ASCIIValue = ASC(mid$(Token, 9))
            if ASCIIValue = 32 or ASCIIValue = 45 then
              SplitToken = mid$(Token, 9)
            else
              SplitToken = mid$(Token, 10)
            endif
          endif
        else 'we want character 4 to 8
           SplitToken = mid$(Token, 4,5)
        endif
      endif
    end function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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