Problem with format of Hyperlink field in an Union Query

[Andydr]

I am having a problem with a hyperlink type field when running a Union query.
I have a field defined in my CLIENT_CONTACTS Table as a Hyperlink Data Type, which is being used for Email addresses.
If I run either of the unionised SELECTS individually, which has this field included, I get the result I am expecting to see. ie just the email address in the format name@company.co.uk
If I bring them together with a UNION then the result displayed is displaytext#address#subaddress ie name@company.co.uk#mailto://name@company.co.uk#
I assume I need to apply some formating in the query if so what?
Is there a good reference book or site where I can get a list of Access SQL syntaxes, I have used SQL against Oracle Databases for many years but have only recently started against Access, so am looking for something that gives me the equivalent syntax in Access for sql syntax ie SUBSTR. Many thanks.

 

[RottPaws]

I'm not sure about your problem with the hyperlink in a union query, but the Access equivalent of SUBSTR is MID.

MID(string, start, number of characters) _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[Andydr]

Thanks RottPaws. If I can crack the Substr, then I can do something with the hyperlink problem, using instr and mid!

The quote at the bottom of your reply reminds me of comments from Oracle Support!