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

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

It worked!

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

Thank you, John

Reply | Quote

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

Reply | Quote

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.

Reply | Quote