Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: MS Access Parsing

    Afternoon peeps.

    Help please on a parsing expression for a query on a non-delimited and non-fixed width value. I have a name field in the format of 'Milne A A', for example which can contain one or more initial(s). I've split the surname into a seperate field without too much trouble but I'm struggling with a query expression to seperate the initial(s) into a single field. I need an expression to give me everything after the first space without actually including the first space.

    I've found examples of expressions, none of which can be directly applied and they distinctly lack explanation of what each part is doing to the text string for me to be able to understand and modify it to my requirement. I've worked out so much by tinkering but still not to the point I need! Can someone offer assistance to my immediate problem and possibly a source of info explaining Parsing in MS Access for me to indulge myself in??

    Thanks in anticipation of any help.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What do you have so far????
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Not an awful lot! I've worked out what some of the example expressions I've seen are doing to a given text string but I haven't homed in on the particular function that I need to use/change to get my desired effect. I've been experimenting with variations of 'Left([Name], InStr(1,[Name]," ")-1)' which gets me everything to the left of the 1st space and removes the space. Getting everything to the right of the 1st space is now the issue and seems surprisingly elusive when the opposite is simple!

  4. #4
    Join Date
    Oct 2003
    Posts
    103

    This seems to work

    Right([data],Len([data])-InStr(1,[data]," "))

    However, if you are trying to break it down to
    first middle last
    returning
    first
    middle
    last

    if they data does not always contain all three you'll have to setup a routine to examine/parse it a few different ways so that
    first last
    comes out
    first
    last

    etc.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    jdostie's will work.

    this will work faster:
    Right$([data],Len([data])-InStr(1,[data]," "))

    this will work faster still:
    Mid$([data],2)

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Spot on. Mid$([data],2) didn't work though- it just removed the first character. The original by jdostie and the amended version work like a charm. Thanks to you both for saving me another bout of RSI! I'll have to buy a book to properly get to grips with parsing no doubt!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    so right!

    i stupidly assumed the 'Milne' was removed (mid$() would work fine with a residual ' a. a').
    dumb on my part - so sorry.

    meanwhile pleeeeeeeeeaase use
    mid$(), left$(), right$()
    rather than their generalised cousins
    mid(), left(), right()
    when you are working with strings.

    the datatype conversion overhead in the generalised form takes 2 * the time of the string-specific function.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    It's no doubt a useful tip to remember then! Cheers izy.

Posting Permissions

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