Convert Query field display from text to hyperlink

[Post Tenebras Lux]

I have a query that draws data from a table that has been imported into
Access 2003 from an SEC file of summary company filings. The fields in that
imported table are:
CIK - and ID field alphanumeric stored as text, e.g. "1375195"
Company Name - stored as text
FileName - stored as text, e.g. "edgar/data/1375195/0001144204-07-060584.txt"

In my query, I'd like to add a field that inserts in front of the FileName
the base URL to the Filename so that it becames a clickable hyperlink, e.g.
FullFileName
"ftp://ftp.sec.gov/edgar/" & FileName
gives the result FullFileName
ftp://ftp.sec.gov/edgar/data/1375195/0001144204-07-060584.txt

How do I convert this last FullFileName
ftp://ftp.sec.gov/edgar/data/1375195/0001144204-07-060584.txt
into a hyperlink data type IN THE QUERY, or into a new table (not an
existing table), so that it is directly clickable.

Right now, it appears in the query result and in a maketable as a text
field. I do this query frequently, and DON"T want to change the table field
data type after each query is run (so please don't suggest this - I know how
to do that - and it works fine, but is labor intensive).

I've tried hyperlinkpart(FullFileName,0) but it only returns a text result,
not hyperlink. The query is run directly from the database container (as of
now), and, for now, I'd like to avoid using a form - as these queries will
ultimately be called from Excel.

I hope this request is understandable - I've tried searching for a
technique, but just can't find anything that directly applies - it seems that
either a form object is used, via VBA, or a table field is converted to
hyperlink. Noone seems to address making the conversion from text to
hyperlink directly in the query.

Thanks for any suggestions.

 

[Ron2006]

I have a query that draws data from a table that has been imported into
Access 2003 from an SEC file of summary company filings.  The fields in that
imported table are:
CIK - and ID field alphanumeric stored as text, e.g.  "1375195"
Company Name - stored as text
FileName - stored as text, e.g. "edgar/data/1375195/0001144204-07-060584.txt"

In my query, I'd like to add a field that inserts in front of the FileName
the base URL to the Filename so that it becames a clickable hyperlink, e.g..
FullFileName
"ftp://ftp.sec.gov/edgar/" & FileName
gives the result FullFileNameftp://ftp.sec.gov/edgar/data/1375195/0001144204-07-060584.txt

How do I convert this last FullFileNameftp://ftp.sec.gov/edgar/data/1375195/0001144204-07-060584.txt
into a hyperlink data type IN THE QUERY, or into a new table (not an
existing table), so that it is directly clickable.  

Right now, it appears in the query result and in a maketable as a text
field.  I do this query frequently, and DON"T want to change the table field
data type after each query is run (so please don't suggest this - I know how
to do that - and it works fine, but is labor intensive).

I've tried hyperlinkpart(FullFileName,0) but it only returns a text result,
not hyperlink.   The query is run directly from the database container (as of
now), and, for now, I'd like to avoid using a form - as these queries will
ultimately be called from Excel.

I hope this request is understandable - I've tried searching for a
technique, but just can't find anything that directly applies - it seems that
either a form object is used, via VBA, or a table field is converted to
hyperlink.  Noone seems to address making the conversion from text to
hyperlink directly in the query.

Thanks for any suggestions.

Have you tried the following on the doublclick event of the FileName
field (or any event that you want)

application.followhyperlink "ftp://ftp.sec.gov/edgar/" &
me.FileName

actually, since the edgar/ part is already in the file name then it
would be:

application.followhyperlink "ftp://ftp.sec.gov/" & me.FileName

Ron

 

[Post Tenebras Lux]

Thanks for the suggestion but I'm not using a form, so there is no Me object
to reference (nor any event property that I can find) in the query builder.
Or are you suggesting something else?

 

[Ron2006]

What I am saying is to NOT attempt to change it in the query.

How the data is stored and how it is used are two different things. If
you have all of the necessary information stored in the field, then
what I was suggesting is that instead of having access issuing the
followhyperlink when you click on a field that is declared a
hyperlink, that you yourself issue the the command in the on dblclick
(or single click) event.

The queries you are executing are saving data not following
hyperlinks.

Ron