This is driving me nuts.

I have a large excel sheet which I am importing to an Access database. I've got several command buttons set up to filter the data in different ways. For some of these I would like to display an email address in my Query result. I would like to be able to click on the email address and have it pop open my outlook.

Changing the field from Text to Hyperlink causes the value to be stored as:
someone@example.com#http://someone@example.com#
You are only able to see this if changing the field to hyperlink, then changing back to text without reimporting the data.

Most of the solutions online involve using VB and having an additional command button to send an email, but one solution on MS's site has to do with an update query. It gave the following:

IIf(Left([Mail Field],8)<>"#mailto:","#mailto:" & Left([Mail Field],InStr(1,[Mail Field],"#")-1),[Mail Field])

It doesn't say if it's storing the information as hyperlink or text, but I'm assuming hyperlink. Looking over their formula, it doesn't seem correct. From what I gather, using the above email address example, the result would always be [Mail Field]. Number one because I feel the first condition should be "#http://" instead of "#mailto:", and even if it was mailto that's not correct because the hyperlink information isn't until after the display text. I changed it to the following:

IIf(Mid([Email Address],InStr(1,[Email Address],"#"),8)<>"#http://",Left([Email Address],InStr(1,[Email Address],"#")-1) & "#mailto:" & Left([Email Address],InStr(1,[Email Address],"#")-1),[Email Address])

In my mind I'm telling it starting from the first "#", if the next 8 total characters are "#http://" I want to change the entire field to someone@example.com#mailto:someone@example.com#, if there is not an entry with http, then just leave as email.

Yet everytime I run this update, I get an error for all cells (due to conversion failure).

Any ideas?

8/31/2009 4:11:37 PM

Import as text and use a form for browsing with an on-click event doing the e-mail launch.

8/31/2009 8:56:11 PM