How do I extra data from a column/field?

[Guest]

I have an access table that has many records and each record has many fields.
One of the fields is a text field of variable length. This field contains
many characters in it including an email address; this email address is
located at the end of the field like this: "234:wkler23, heliuberd,ss
rs99rere: (e-mail address removed)"
I would like to extract only the email address, from each field for all the
records on this table, and insert the email address into a brand new field,
on a different table.

Could someone please help me build the sql code that would accomplish this
task?

I would like to express my gratitude to those kind souls that can help me
solve this task.

Thank you very much.

Regards,

Enrique

 

[Steve Schapel]

Enrique,

Try it something like this...

INSERT INTO DifferentTable ( NewField )
SELECT Mid([YourField],InStrRev([YourField]," ")+1)
FROM YourTable

 

[Mike Schlosser]

Since the email is always at the end of the field, and is seperated by a
SPACE ??,
You can look from the end of the field, towards the from, for a SPACE.
From that point in the field,+1, to the end would be the email address.
If the data is separated by a comma of something else, make the changes to
the line below.

mid([FieldWhichContainsEmail], InstrRev([FieldWhichContainsEmail], " ")+1)

Make a select query and place the above line in the Field to display.
Or make an update query and place this into a new Email field.

Mike Schlosser