Convert Query field display from text to hyperlink

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
Utf
1/4/2008 3:16:03 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
5036 Views

Similar Articles

[PageSpeed] 14

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

Similar Artilces:

largest value of field in query output
I have a query that pulls all the records from a table. For example, the query outputs the following: TITLE REV POSITION DATE methodA / 0 / tech / july 5 methodA / 1 / tech / july 10 methodB / 0 / tech / july 5 methodB / 1 / tech / july 10 methodB / 2 / tech / july 12 methodC / 0 / tech / july 6 methodC / 1 / ...

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

"Functions Displaying", & Old Excel 97 ?
Hello, Really embarased to ask, but: Using an old copy of Excel "97" Where do I find the list of Functions that are available (on what pull-down menu), and how do I display them, please ? Just can't seem to find them on any of the items that displays. How can I get an icon to allow easy access to them on one of the toolbars ? Thank you, B. Excel's help is nice(?). Look for Functions. and... Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ Norman Harker has his version at Debra Dalgleish's si...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

Disapearing Query Results!
Hello Dear I have some Combo Box's on a form that extract data from queries When queries run,the results show to the user that is not necessary! Is it possible to hide query results from user? Thanks Best Regards The question is unclear. Do you want to show the results of a query in the form? Are you seeing query results in data sheet view and don't want to? If this is the case, then you must be executing the query in some manner and opening the query. We cannot see your application and cannot see what you are doing. John Spencer Access MVP 2002-2005, 2007-2...

creating custom fields for contacts
I want to create custom fields in the 'details' section of contacts. I was able to create a new section but when select the 'Add Fields' I can only choose from the fields created by Microsoft. Ideally I would like to be able to create a custom field and determine if it needed a pull-down or user entry or user selected (like a simple yes/no) Is this possible? Thanks Rob Jayson Hi Rob, It is very easy to create custom fields and include them in existing forms. You can create new fields or attributes as they are called, by clicking on Settings->Customization-> ...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

What are the advantages of a query using a derived table(s) over a query not using them?
I know how derived tables are used, but I still can=92t really see any real advantages of using them. For example, in the following article http://techahead.wordpress.com/2007/1= 0/01/sql-derived-tables/ the author tried to show benefits of a query using derived table over a query without one with an example, where we want to generate a report that shows off the total number of orders each customer placed in 1996, and we want this result set to include all customers, including those that didn=92t place any orders that year and those that have never placed any orders at all ( he=92s ...

diagonal text
HOW DO I MAKE MY TEXT GO IN A DIAGONAL WAY ACROSS THE PAGE On Sun, 30 Oct 2005 11:06:07 +0100, SCOTT wrote (in article <8844C60C-FD08-4FB5-B289-D530CAA285DA@microsoft.com>): > HOW DO I MAKE MY TEXT GO IN A DIAGONAL WAY ACROSS THE PAGE Lose the caps. It's considered yelling, is incredibly rude and makes your posts very hard to read. Please explain what it is you're attempting to accomplish and your Publisher version. By the way, questions end with question marks (?). What version of Publisher are you using? In 2000, arrange, rotate or flip, custom rotate, input your n...

Unintended field "expression 1006" in the query grids
Hello, When I open my query, there is an unintended field "Expr 1006". I do not know why is this automatically created. Anyone tells me, why this happened. -- H. Frank Situmorang Hi Frank this usually means that you put the same field into the query twice. Jeanette Cunningham "Frank Situmorang" <hfsitumo2001@yahoo.com> wrote in message news:2CF86F30-0E4D-499B-9FE0-D62BF450BC57@microsoft.com... > Hello, > > When I open my query, there is an unintended field "Expr 1006". I do not > know why is this automatically created. Anyone tells me, w...

how to convert excel's .cvf file to .csv file
...

Can I use the value of a field as predeterminate value of another.
I am making a table to know who is working in each surgery in a heath center. For each day, I have a field "owner" (of the surgery) and another "working" (the one that really works that day there). But the majority of the days, the "owner" is who works in his surgery, so I want to establish the value of "owner" as predeterminate value of "working" . Is it possible? Thank you Not directly in the tables. You could do this in a form by making the value of one text box the default value of another text box. -- --Roger Carlson MS Access M...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

Outbox displays Received Date
Need some help on this one. I'm using Outlook 2000 on a Windows XP machine and for some odd reason the Outbox now displays the "Received" date and the Inbox displays the "Sent" date. How do i correct this. It seems to be hidden in the PST file because when I login on a different machine it shows the same thing. Right click on the column headers and choose the "Field Chooser". Drag the field you want to see from the Field Chooser into the column headers area. Drag column headers you don't want to see away from the column headers. "Lee" ...

change case of text #2
Some recent data was imported to an Excell worksheet that had a large amount of text in capital letters. Is there a way to change this data to "PROPER" throughout the entire worksheet? Thank you Gloria Anderson at Habitat for Humanity of Key West and the Lower Florida Keys =proper(range) will do the trick Mike "habikeys" <anonymous@discussions.microsoft.com> wrote in message news:017801c39f21$a016ece0$a501280a@phx.gbl... > Some recent data was imported to an Excell worksheet that > had a large amount of text in capital letters. Is there a > way to c...

Add help text to a form field?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I'd like to give people instructions about how to fill in form fields in the form itself. I tried &quot;adding help text,&quot; but nothing shows up in the form field. Is what I am trying to do possible, and, if so, what am I doing wrong? What you're using appears in the Status Bar when the field is accessed. Unless people know to look there it does practically no good. IMHO it's more effective to include instructional text as default content for the field or provide explicit labeling in an adjacent table c...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

Cannot display all datas in Line Chart
Hi everybody, I have a chart report , the type is line chart. It shows the values per half hour. the X axis is time (Per Half Hour) , the Y axis is the value for that time. it works well when I select the time between 130 Days , but it wont show the more than 130 days. eg : when I choose to show the data from Oct 22, 2007 to Feb 22, 2008 it shows the data from Oct 22,2007 to Feb 02 , 2008 I cannot get the accurity end data but I can guess it from the values in X axis the values are : Oct 22, Oct 26, Oct 31... ...Jan 22 , Jan 24 , Jan 27 , Jan 29 Please if some one can tell me does the...

Importing text from an Explorer window
Howdy all. OS: XP, Excel 2002 SP3 I have my music stored in the My Music directory. I have Artist, Album Title, etc. attributes turned on. What I'd like to do is to take all of this information and input it into an Excel spreadsheet. When I "Select All" from the window, and "Copy", it wants to copy the actual songs, and not the text. Also, the "Paste" option in Excel is grayed out anyway. Is there a way that I can treat the info in a Browser window as just text, and then copy that info into a spreadsheet? TIA. -- Don Cox Here is one I have used...

Change color of row (Col A thru Col Q) based on text value in Col J
I am using David McRitchie's code for changing color of entire row based on contents based on a specified cell text value: 'Target.EntireRow.Interior.ColorIndex = 36'. This works fine; however, I only want to change color in the first 17 cells in each of the affected rows. How do I do this? Also, I am confused: do I want the stmt 'Application.EnableEvents = True' at the top of my coding in the 'Worksheet_Change' event coding (occupies the Sheet1 Module)? One way: Target.EntireRow.resize(1,17).Interior.ColorIndex = 36 JingleRock wrote: > > I...

Memo Fields
I'm trying to collect data for a multiple record update and place it into a memo field. The application steps through a record set compiling the data to a text box on a form while not eor Me![Text1] = Me![Text1] & ", " & Me![RecordData] Wend This works fine. When I look at Me![Text1] after the While loop all the data is correct. I then place this filed into a table using a simple append query, but when I look in the table the information is truncated with many non-alpha charaters at the end. Any thoughts? On Tue, 26 Feb 2008 08:54:01 -0800, Justin <Justin...

XML Note convert to DataSet
Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

Crosstab query question
I use a crosstab query in a database as a first step in recalling multiple monetary transactions made on the same day that, when added together, exceed $10,000.00 and then generate a report based on the results. The query currently returns multiple transactions made on the current day as per the design. The OrderDate field in the query's design view is as follows: Field: OrderDate Table: Transaction Total: Where Crosstab: Sort: Criteria: "Date( )" Or: I would like to be able to bring this data up from dates in the past a...