Some sort of "Find and replace"

[Guest]

Hello,

I need to update a text field in a table which contains strings of variable
lenght separated by % character: AAAAA%BBBBB%CCCC.
How can I create a query or a function to keep in that field just the BBBBB
string?
In Excel I used find and replace:
- find A*% and replace with NULL
- find %C* and replace with NULL and I have just BBBBB string
How can I do the same thing in Access?

THX

 

[Jon Lewis]

Have a look at the InStr, Left, Mid, Right functions.

 

[Douglas J Steele]

Is it always going to be 3 strings separated by % and you always want the
2nd string?

You can use the Split function:

Split(MyString, "%")(1)

will give you the 2nd string (it starts counting at 0)

 

[Guest]

Sometimes the first and the last string are missing...
And I have to work with Access 97

Thanks

 

[Douglas J Steele]

Well, it's possible to write a Split function for Access 97, but if you
don't know the number of substrings in advance, you're probably better off
using the InStr and Mid like Jon suggested.