Convert Query field display from text to hyperlink

  • Follow


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.
0
Reply Utf 1/4/2008 3:16:03 PM

On Jan 4, 8:16=A0am, Post Tenebras Lux
<PostTenebras...@discussions.microsoft.com> wrote:
> 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. =A0The fields in =
that
> imported table are:
> CIK - and ID field alphanumeric stored as text, e.g. =A0"1375195"
> Company Name - stored as text
> FileName - stored as text, e.g. "edgar/data/1375195/0001144204-07-060584.t=
xt"
>
> 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/00011442=
04-07-060584.txt
>
> How do I convert this last FullFileNameftp://ftp.sec.gov/edgar/data/137519=
5/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. =A0
>
> Right now, it appears in the query result and in a maketable as a text
> field. =A0I do this query frequently, and DON"T want to change the table f=
ield
> data type after each query is run (so please don't suggest this - I know h=
ow
> 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. =A0 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 t=
hat
> either a form object is used, via VBA, or a table field is converted to
> hyperlink. =A0Noone 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


0
Reply Ron2006 1/4/2008 6:18:07 PM

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" wrote:

> On Jan 4, 8:16 am, Post Tenebras Lux
> <PostTenebras...@discussions.microsoft.com> wrote:
> > 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
> 
> 
> 
0
Reply Utf 1/4/2008 10:08:02 PM

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
0
Reply Ron2006 1/7/2008 1:41:39 PM

3 Replies
2732 Views

(page loaded in 0.098 seconds)


Reply: