Some sort of "Find and replace"

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
Sometimes the first and the last string are missing...
And I have to work with Access 97

Thanks
 
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.
 
Back
Top