Convert value to HTML

[expensive]

Hello

I've got a query which creates a URL address by prepending and appending a value. Nothing shocking so far, but the field is returned as a text datatype. How would I go about making the value return as a working HTML URL?

I guess I'm looking for a way for Access to recognise that if a value begins '

http://'

then to turn that in a HTML link, else return as text.

Is this possible within plain old JetSQL/Access, and if so, how?

 

[jrbarnett]

What are you presenting this data in? A report, form, query, writing to plain text file on disk?

what needs to be done depends on this.

John

 

[expensive]

hello.

it's a query, which I either copy and paste to a XLS spreadsheet, or I export to XLS. In both instances, I'm looking for a way to automatically make a field display as working HTML. The URL address is generated autonatically from the query, it just doesn't present itself as HTML.

Is that clear?

 

[jrbarnett]

Does the rest of the data in the query with this make up an HTML table?

John

 

[expensive]

hello,

no, it's not for a HTML table, it's just a query, and one of the fields creates a web address from the data available. I'd just like for that address to be clickable and to actually work, both from Access query view, and should I copy and paste to Excel, or export as Excel. The rest of the query can remain as data.

Here's an example of what my query is doing,

IIf(tbl.[ID] Is NOT Null,
'

http://www.website.com/?id='

+ tbl.[ID] + '&s=somevalue',
IIf (tbl.[ID] Is Null,
'No Link'
)
)

Thanks

 

[jrbarnett]

Could you change the underlying field data type from text to Hyperlink or would this affect lots of other things as well?

John

 

[expensive]

hmm, so it could be as simple as a CAST()?

IIf(tbl.[ID] Is NOT Null,
CHTML('

http://www.website.com/?id='

+ tbl.[ID]) + '&s=somevalue',
IIf (tbl.[ID] Is Null,
'No Link'
)
)

I've made up CHTML as I don't know the exact function to do this, if possible. Does anyone know if I can CAST from a string to hypertext?

 

[jrbarnett]

Before proceeding with this -is there anything else that relies on this Access database? If there is, don't do the following.

Go into the Access database window and click the tables tab.
Highlight your table
Press Ctrl C to copy it
Press Ctrl V to paste it. Give it a name something like "Copy of yourtable"
You now have a backup of the original with all your data in.

In the new table, right click the table and choose Design.
You will see a screen come up with a list of fields.
Against the field that holds the text data, it will have the word Text (or Memo, but this is less likely) next to it.
From this drop down list, change it to Hyperlink.
Save the table, go back into your query and try again.

John

 

[expensive]

hi, I can see what this has done, but it hasn't made the whole expression Hyperlink data type. Just making the tbl.[ID] column hasn't then implicitly cast the rest of the expression as Hyperlink data type.

Can you think of anything else?

 

[jrbarnett]

Does the ID field hold the web URL, or is the URL held elsewhere?

John

 

[expensive]

no, the URL is created by prefixing the tbl.[id] field. So, while I can change the datatype of the tbl.[id] to hyperlink, this doesn't automatically change the expression that uses tbl.[id] into a hyperlink datatype.