Some sort of "Find and replace"

Hello,

I need to update a text field in a table which contains strings of variable
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12228-some-sort-of-find-and-replace.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
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

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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


"Adidre1" wrote in message
news:CE4E196F-4D59-45AB-9A75-2E9B69FDBCEA@microsoft.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
> - 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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Adidre1" wrote in message
news:CE4E196F-4D59-45AB-9A75-2E9B69FDBCEA@microsoft.com...
> 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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
>
> THX

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

Thanks

"Douglas J Steele" wrote:

> 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)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
> (no e-mails, please!)
>
>
> "Adidre1" wrote in message
> news:CE4E196F-4D59-45AB-9A75-2E9B69FDBCEA@microsoft.com...
> > 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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
> > 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
>
>
>

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.

--
Doug Steele, Microsoft Access MVP
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
http://I.Am/DougSteele
(no e-mails, please!)


"Adidre1" wrote in message
news:3BAC1917-4C10-4A61-B99E-8F75171A6D6B@microsoft.com...
> Sometimes the first and the last string are missing...
> And I have to work with Access 97
>
> Thanks
>
> "Douglas J Steele" wrote:
>
> > 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)
> >
> > --
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12228
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Adidre1" wrote in message
> > news:CE4E196F-4D59-45AB-9A75-2E9B69FDBCEA@microsoft.com...
> > > 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
> >
> >
> >