Email Address in Concatenated Query Field

I have included an email address in a concatenated query field (the table field type for the email address is a hyperlink). I would like the email address to remain a clickable hyperlink in the query, but this is what happens:

Term >>  Email:[emailaddress]
Result >>
(email address is a clickable hyperlink, as desired)

Term >> NameAndEmail: [Name] & ":  " & " "& [emailaddress]
Result: >> John Smith:
(email address gets broken into its component parts, and is no longer a hyperlink)

Does anyone know of a way to fix this ?
Paul McCabeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul McCabeAuthor Commented:
Sorry, that second term should have been:
NameAndEmail: [Name] & ":  " & [emailaddress]

.....dosen't change the problem though.
Jeffrey CoachmanMIS LiasonCommented:
There is no real fix because this is not how hyperlinks were designed to be used.
What you are asking for cannot be done "easily".
Technically only a "followable" address can be click-able.

So while an email server can "follow":
...It does not know what the string below means, or how to interpret it.:
    George Washington;

To my knowledge you cannot even have one concatenated string having one part click-able and the other  part not click-able.

To be fair, can display the "ugly" concatenated field in a Form, to look like a hyperlink and be click-able (set the IsHyperlink property to Yes)
But the this data will still fail when you click on it, because it is not a true hyperlink. (and again, it will still look "ugly")

Another question is why this concatenated field needs to be click-able in the query?
Anytime you need user interaction (clicking on things), ...use a form.
Why not just display the two fields side by side, in a form, ...this way they are both seen together, but the email hyperlink will look correct and will still be click-able?

And to really wrap this up, ...note that many Access developers do not use the Hyperlink datatype because of these issues. (and also that when you move to SQL server, Hyperlinks are not directly supported at the table level.)
In Access, the email address in the table will simply be a text field of:
...Then in a form you can set the IsHyperlink property to Yes.
Then use code like this to activate it:
Application.FollowHyperlink YourEmailFieldName
...but this is all optional info, ...not directly related to your question...

As always,...ultimately you can do anything you want, complicated the system will need to become achieve your goal is another story...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
But,...lets see if other experts may have a solution to your issue directly.
(In case I misunderstood something)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Paul McCabeAuthor Commented:
Thank you for your comments. In fact, the direction you suggest of ditching the Hyperlink datatype in the table and making the email address functional in the form using the IsHyperlink property plus coding solves my problem.  As a side-note, it seems the code you mention above is for website links, as I couldn't get it to work with email addresses. I got it to work with emails using the following: Application.FollowHyperlink "mailto:" & Me.emailbox. Thank you again for your help !
Robert ShermanOwnerCommented:
[EDIT: This was posted after solution was accepted.   Figured no point in deleting my comment at this point, for future readers.]

As Jeffrey has already well explained, a "fix" really depends on what the ultimate usage goal is for what you're attempting.

Queries should be more of a means to an end and not the end itself, and when you get into things being "clickable" in a query I wonder if there's a better approach such as a form (or even report).

When you get into email addresses that are hyperlinked (as mailto: URLs) you also further depend on the operating system environment being configured to properly handle the action.   That may be fine if you are limiting distribution of the database to a small set of known and locally administrable systems, but could break down with wider distribution as not all systems will be readily capabable of doing something useful with the mailto: action.  

As you have already discovered when doing the concatenation, even Access itself will only handle the Hyperlink field type to an extent.  What you get when you start concatenating is the underlying string representation of the Hyperlink type, which follows a four-part scheme of ["display text", "address", "subaddress", "screentip"] separated by hash symbols.   So, doing anything "exotic" with this fieldtype will require you to parse out these pieces of the field and then write your own code to use this info accordingly for user display and click action, etc.  

It could be that what you want is simply to parse out the second parameter of the Hyperlink type, which will get you the "mailto:..." part that is stored in the field.   In code, I believe there is a Hyperlink object that has these properties easily available, so that you could get to the hyperlink address, for example, with the expression FieldName.Address.   You might even be able to get at this directly in a query by using [emailaddress].Address (based on your example) but I haven't tested this to confirm.

Give us a bit more info on what your hoping to achieve in the end, and perhaps we can help you hone in on a workable solution.
Paul McCabeAuthor Commented:
Thank you for your response Robert. Sorry things had just wrapped up by the time your message arrived, but your comments should be useful for future readers.
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.