need to parse data in one field into two fields

[taxmanandy]

my linked database has an email fields that shows data as joe@gmail.com#m ailto:joe@gmail .com#. I want to strip the #...# so new field is joe@gmail.com. i am a access novice and need help
thanks
Andy

[NeoPa]

Do you understand why it's in that format in the first place?

Your question is very minimalist and without much information. As such it's hard to provide a helpful answer. I would suggest some SQL that strips out only the part you're interested in, and adds it into wherever it is you want it.

As I say, knowing so little of where that might be makes it very hard to give a useful answer.

[ADezii]

First, a couple of Assumptions:

1. Table Name is tblEMail.
2. Field Name is [Address].
3. All Records have the same, consistent Format.
4. You wish to extract the mailto Component without the #.
5. This is one of several Methods:
   

   SELECT tblEMail.Address, Replace(Mid$([Address],InStrRev([Address],":")+1),"#","") AS EMail
   FROM tblEMail;

6. Records in tblEMail ([Address] Field):
   

   Address
   ANewmann@MAD.com#mailto:ANew@gmail.com#
   BGates@Microsoft.com#mailto:BillGates@Yahoo.com#
   Fred@Flintstone.org#mailto:FFlintstone@Verizon.net#

7. Results after executing Query:
   

   Address	                                            EMail
   ANewmann@MAD.com#mailto:ANew@gmail.com#	            ANew@gmail.com
   BGates@Microsoft.com#mailto:BillGates@Yahoo.com#       BillGates@Yahoo.com
   Fred@Flintstone.org#mailto:FFlintstone@Verizon.net#	FFlintstone@Verizon.net

[taxmanandy]

that worked perfect

thank you

Andy

[ADezii]

You are quite welcome.

[taxmanandy]

did not work after all

i tried your command at home using access 2003 with a mdf file and it worked fine. i tried the same command at my office using access 2007 linked to a accdb file and i got this error message

Invalid text or symbol.
Error in list of function arguments: '""' not recognized.
Unable to parse query text.

any thoughts

Andy

[NeoPa]

You may have your ANSI standard compatibility set differently. In which case you'd need to use the proper quote characters. See Quotes (') and Double-Quotes (") - Where and When to use them.