Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help Wih Parsing a Field in a Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following path information in a path field in a table:

    f:PropertiesStreetsAbbott Rd RearAbbott Rd Rear 1245
    f:PropertiesStreetsAbbott Rd RearAbbott Rd Rear 2019
    f:PropertiesStreetsAbbott RdAbbott Rd 1201
    f:PropertiesStreetsAbbott RdAbbott Rd 1219

    What syntax do I put in query to return the following:

    Abbott Rd Rear 1245
    Abbott Rd Rear 2019
    Abbott Rd 1201
    Abbott Rd 1219

    The portion of the field Iím looking for always appears after the 4th (right slash)

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Wih Parsing a Field in a Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    Isn't it time to upgrade to a newer version of Access? In Access 2002 and later, you can use the function InStrRev to find the position of the last backslash in the text:

    Mid([FieldName], InStrRev([FieldName], "") + 1)

    In Access 2000, you can use

    Mid([FieldName], InStr(InStr(InStr(InStr(1, [FieldName], "") + 1, [FieldName], "") + 1, [FieldName], "") + 1, [FieldName], "") + 1)

    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help Wih Parsing a Field in a Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> It worked!

    Still have 7 clients using a2k, only want to sing out of 1 hymn book.

    Thank you, John

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help Wih Parsing a Field in a Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    The requirement has changed, I now have a trailing following path information in a path field in a table:

    f:PropertiesStreetsAbbott Rd RearAbbott Rd Rear 1245
    f:PropertiesStreetsAbbott Rd RearAbbott Rd Rear 2019
    f:PropertiesStreetsAbbott RdAbbott Rd 1201
    f:PropertiesStreetsAbbott RdAbbott Rd 1219

    What syntax do I put in query to return the following:

    Abbott Rd Rear 1245
    Abbott Rd Rear 2019
    Abbott Rd 1201
    Abbott Rd 1219

    How do incorporate something like the following?
    AddressName: Left([Address],InStr([Address],"")-1)

    Into the following line the eliminate the trailing
    Address: Mid([FilePath],InStr(InStr(InStr(InStr(1,[FilePath],"")+1,[FilePath],"")+1,
    [FilePath],"")+1,[FilePath],"")+1)

    Thanks, John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Wih Parsing a Field in a Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8

    I'd do it as you indicate, in two steps: first

    Adress: Mid(...)

    then

    AddressName: Left([Address],InStr([Address],"")-1)

    or

    AddressName: Left([Address],Len([Address])-1)

    It is possible to do it in one step, but the formula becomes really horrible.

Posting Permissions

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