I have an email field that automatically stores data as a mailto hyperlink:

#mailto:joe@abc.com#

Is there an expression I can use to manipulate the data so that it returns just:

joe@abc.com

..on a report? I've tried several different "Trim()" functions, but I can't seem to get the syntax right to get it to work.

Any help would be appreciated.

Try (one of many ways):

strEmail="#mailto:joe@abc.com#"

strEmail=Left(strEmail,Len(strEmail)-1) 'Strip off trailing #
strEmail=Right(strEmail,Len(strEmail)-8) 'Strip off #mailto:

strEmail now contains just the email address

You could reduce this to one line by using Mid function:
strEmail=Mid(strEmail,9,Len(strEmail)-9)

Thanks for looking into this problem for me. I'm still getting #Error after inputting in what you had suggested. The logic of your expression makes perfect sense, so I must be doing something wrong.

I'm laying out a report and I'm basically inputting your expression into the "control source" property of the email field. However, I keep getting #Error. Is there a step I am missing?

Thanks

First of all - I would suggest that you go to the table and strip the # signs out of the table, before feeding the data into anything else. The # sign is used to designate a date and what Access is doing is assuming that you are representing a date in the string. Probably just automatically run two update queries in the processing.

Just my 2 centavos....

I'm afraid that I need to retain the "#'s" on the data, as I need for the data to be stored as mailto links in my database so that when a person clicks on the field, it opens up the default email application.

My problem is pulling the data and presenting it on a regular report for printing. It looks silly if the report displays "#mailto:joe@abc.com#" instead of just the email address. I tried bc301's initial suggestion by pasting the MID() expression into the VB code, and it worked, except for that it stripped out the #mailto:# in the original data too. I'd like for the data to remain intact, but have it displayed as joe@abc.com in just the printed report.

Any other suggestions? Thanks.

Are you making the controlsource property "=<formula>" ? Is the name of the box different from your controlsource and any ohter boxes, and or captions.

Just asking the dumb questions?

Yes, the name of the box and controlsource are the same. In the controlsource property, I have been trying different "=(formula)" expressions "(Left(), Right() Trim())", but with no success (#Error).

Any suggestions? Thanks again.

Change the names to be different and try that. You can get the errors from objects being named the same as the caption, titles, and controlsource, text boxes.

Maybe the first thing in the formula would be to make it
=Mid(cstr(strEmail),9,Len(cstr(strEmail))-9)

Jim P.,

Hey, it worked! Thank you so much for figuring out this problem! Hope your company pays you well, because you deserve every penny! Thanks again for your expertise!

E. Liao

Pay is never well enough...but I have a job.....

Glad it worked.