Substring and Charindex fuctions in Access

[Ulrich Sommer]

Hallo,
I would like to isolate a substring from a longer string.
The starting position of the substring is defines by a
letter combination.
Example: email-Address in a Full Name filed

Full_Name
"Paul Lehmann; email[email protected]"
"Hans Muller; email:[email protected]"

I would like to see in these felds evereything right
of "email:"
In SQL Server, there are Substring- und Charindex
Funktions that can do this.

In this case,

SELECT
SUBSTRING (Full_Name,CHARINDEX('email:',Full_Name),100)
AS Email
From TableName

returns

Email
"email[email protected]"
"email:Hans.Mü[email protected]"

Can I build something like this also in Access XP? What
are the names of the functions here?


Thanks

 

[Michel Walsh]

Hi,

InStr( astring, bstring ) returns the position of the first occurrence of
bstring in astring. Returns -1 is no match is found. There is also InStrRev
that looks from the end rather from the start. See the help file for more
information.

Mid( astring, i, j) returns the substring of j characters starting at
position i (first position = 1) in astring.


Hoping it may help,
Vanderghast, Access MVP


Hallo,
I would like to isolate a substring from a longer string.
The starting position of the substring is defines by a
letter combination.
Example: email-Address in a Full Name filed

Full_Name
"Paul Lehmann; email[email protected]"
"Hans Muller; email:[email protected]"

I would like to see in these felds evereything right
of "email:"
In SQL Server, there are Substring- und Charindex
Funktions that can do this.

In this case,

SELECT
SUBSTRING (Full_Name,CHARINDEX('email:',Full_Name),100)
AS Email
From TableName

returns

Email
"email[email protected]"
"email:Hans.Mü[email protected]"

Can I build something like this also in Access XP? What
are the names of the functions here?


Thanks