Creating a hyperlink from two separate table fields

[Remster]

Hi Folks



Is it possible to create a hyperlink in a query from three* separate table fields, e.g. SiteName, URL and Screentip? I can't work out the code. For example, 'Hyperlink: [Table.SiteName] & "#" & [Table.URL] & "##" & [Table.Screentip]' just generates 'Website#www.website.com#Open_Website' in the query.

The reason why SiteName, URL and Screentip are separate fields is that I want the site name, URL and screentip to be input separately in the form that's used to populate the table.

Thanks

Remster

*Edit

[ajetrumpet]

Remster,

This is not to be done at any 'dataset' level. What you'll want to do is just concat the fields, leave the string values in the query as is, then on your forms and reports, color and underline the text appropriately. you will want to make it a hyperlink with an 'on-click' event and one line of code:

application.followhyperlink screen.activecontrol.name

(code may not be 100% there). I'm not sure if the 'hyperlink hand' cursor type is available in 03, but it is in 07. That's another that can be changed to make the user think it really is a hyperlink.

and then of course, when you say 'screentip', if you're really talking about a webpage bookmark (anchor), you'll have to concat in a pound sign in the actual value at the query level so the user will be taken to that part of the page when clicking.

[Remster]

ajetrumpet

Many thanks for getting back to me.

Just a point of clarification before I enquire any further. By '"dataset" level' are you referring to tables or to both tables and queries? What I want are three separate fields in my table (SiteName, URL and Screentip) and the same three separate fields in the corresponding form, but a combined hyperlink field in my query, like this: Website. I need the hyperlink field in the query because the query is to be exported to HTML to create the contents of a Webpage.

An acceptable alternative would be to have a fourth, hidden, field in the form that calculates the hyperlink from the values entered in the SiteName, URL and Screentip fields and sends it to a fourth field in the table. I can't work out how to do this though!

By the way, I really do mean 'Screentip'. According to MS Access Help, the structure of a hyperlink is 'displaytext#address#subaddress#screentip'.

Remster

[ajetrumpet]

okie dokie.

well...I don't think you can do that from within a query. could be wrong though.

and yes....'dataset' means 'query' or 'table'. so...if you want to export this stuff out via html, I don't think you can it either, because a database outputted to html through the wizard will automatically read the table fields AND their values as just that, values. It will not recognize an html hyperlink syntax, nor will it recognize pound signs for what you're thinking about them.

However, you can export through code and use vba to write the file yourself and completely forego the wizard. If you do it that way, you can make the values in the dataset whatever you want. Here is Allen Browne's suggestion for doing so: http://allenbrowne.com/AppOutputHtml...tml#OutputHTML

[Remster]

I've been directed to that Allen Browne link before, but I didn't really understand what to do. I'll have another look in the next couple of days.

The way my experimental database works at the moment (this is very much WIP) is that the user enters 'displaytext#address##screentip' into the form, and then both the table and the query contain the resulting hyperlink ('display text'). The HTML page generated from the query is then exactly as I want it. Is it all right to have hyperlinks within tables?

If so, what about the possibility I suggested of combining the displaytext and address in an invisible field in the form. Would that be possible? Then the user would enter them into different fields but they'd be combined before reaching the table.

[ajetrumpet]

Is it all right to have hyperlinks within tables?

This is the last time I'll say this: NO. I said it 3 times already. version 07 has a hyperlink field, but you can't manipulate what it outputs via an export (code wise). I don't think.

If so, what about the possibility I suggested of combining the displaytext and address in an invisible field in the form. Would that be possible?

YES. and AGAIN, it doesn't matter what you put in field a, b, c, or field 500. combine whatever you want, whenever you want. I think the point that you're missing here is that Access will not recognize this line for anything:

www.domain.com#mysection#myothersection

if you compare the interpretation abilities of the Jet engine with HTML parsing, they are completely unrelated. Pound signs mean nothing in Access within the context you're talking about. (as far as I know).

if that doesn't make sense, what that means is that this:

www.domain.com/#bookmark

written inside of an <a> tag in HTML is 100% opposite from the same code written in an Access table. In Access, Jet reads that code as a string, nothing else. To Access, it is simply 24 bytes of memory to process. make sense?

and...if I haven't already covered this, you need to understand that if you're eventual output in a field is going to be this:

site#url##screentip

that means absolutely nothing until you get it into a program that is designed to interpret it properly. e.g. - "go to this website and scroll down to this section".

HTH

[Remster]

Is it all right to have hyperlinks within tables?

This is the last time I'll say this: NO. I said it 3 times already.

But the question I'm asking has changed! Originally I was asking whether it's possible to combine two fields by means of a query to create a displaytext hyperlink. Now I'm asking whether it's advisable to enter displaytext hyperlinks into a table. Despite what you say, I know it's possible to do this, because I've done it; and I've successfully exported the data to an HTML document containing the hyperlinks. Here's what I entered into the table, in a field with the Data Type 'Hyperlink':

Google#http://www.google.co.uk/

And here's what came out in the HTML document:

<A HREF="http://www.google.co.uk/">Google</A>

What I'm trying to find out now is whether I'm storing up any problems for myself in having a field with the Data Type 'Hyperlink' in a table (just as I was warned I would be if I had a field with the Data Type 'Lookup').

[W]hat about the possibility I suggested of combining the displaytext and address in an invisible field in the form. Would that be possible?

YES.

Then please tell me how! If it doesn't work, so be it. But at the moment I can't even work out what I have to do – what code to write or where to stick it.

[ajetrumpet]

I appreciate your persistence Remster.

Here's what I entered into the table, in a field with the Data Type 'Hyperlink':

Google#http://www.google.co.uk/

And here's what came out in the HTML document:

<A HREF="http://www.google.co.uk/">Google</A>

What I'm trying to find out now is whether I'm storing up any problems for myself in having a field with the Data Type 'Hyperlink' in a table (just as I was warned I would be if I had a field with the Data Type 'Lookup').

IMO, NO you're not. I don't see anything wrong with hyperlinks. Never heard much news about them being evil.

hat about the possibility I suggested of combining the displaytext and address in an invisible field in the form. Would that be possible?

you have this in your query field (as state by you, above):

Google#http://www.google.co.uk/

what do you mean by COMBINE? concat? Or make it actually 'be' a hyperlink on the form?

I would assume the latter. if that is the case, bind a textbox on your form to the query field. it's as simple as that. the hyperlink will display automatically. make sense?

I can't remember what the properties are of those types of fields, but check them out for yourself. if there is a 'display text' property, obviously you'll have to cut out the "google #" characters because that looks bad.

so, get a field on that form, click the link and see what it does!

[slave138]

I think this might be something like what you're describing:

[slave138]

If you only want the final links displayed when exporting to HTML, create a query to only show the desired fields (probably with aliased headings) and then export from the query instead of the table.

[Remster]

slave 138 has the answer! For anyone who's interested, see the attachment two posts ago. Thank you.

ajetrumpet, many thanks for sticking with it and sorry for not explaining myself very well. For what it's worth, I think I meant 'concat', but I couldn't get it to work.

[slave138]

Happy to help

[Accesser]

I think this might be something like what you're describing:

Thank you slave138! This is just the solution I was hunting for.