Imported email adress format problem

[George Bowyer]

I have imported a table of contacts from an Excel spreadsheet into a "temporary import" table in my database. That all went fine.

I then ran an append query to add the records in my "temporary import" table into my contacts table.

The import table has email addresses in a "short text" field.

I am importing them into a "hyperlink" field in the Contacts table

When I look at the imported email address in the contacts table, the text is blue and highlighted, but does not "click" to launch outlook as it should.

If I right click and "edit hyperlink" the address is there in the "text to display" but not as Mailto: in the "address"

If I cut and paste the text back into the field, it reappears with "##" appended at the end.

If I delete the "##" then the hyperlink works as it should and remains valid.

If I don't delete the "##", it disappears again after the field has lost focus and the hyperlink does not work.

I don't want to have to do that to very record as there are over 1,000

Can anyone help me sort this out, please?

 

[June7]

Hyperlinks are supposed to be composed of 3 parts separated by # character. Review http://allenbrowne.com/casu-09.html and http://allenbrowne.com/func-GoHyperlink.html

You can use an UPDATE action to modify the email string to a hyperlink structure.

 

[George Bowyer]

Thanks.

This is what I used. Might not be the most efficient, but it worked:

strEmail = rs1!fldEMail1

strFullEmail = strEmail & "#Mailto:" & strEmail & "#"

rs1.Edit
rs1!fldEMail1 = strFullEmail
rs1.Update

 

[June7]

Could have used an UPDATE query object instead of VBA but as long as it's resolved ...