Trouble with Intranet pulling hyperlink field from Access database

[cdck]

This is related to a previous thread in the Microsoft Access Queries and Jet SQL forum, number 701-1228509.

For background, I am working on a company intranet which tracks documents. The document information is stored in an Access database, which is used by the people who actually control the documents. One field in the database contains the location of the file for the documents to which it is attached. This field is formatted as a hyperlink.

The problem that I encountered came up when I was forced to change my version of Access from 2000 to 2003. All data stored in these fields before this 'upgrade' still functions properly when queried by the intranet. New entries, however, return both the display text and the address, with no delineation between them. When this text is inserted as the address in a hyperlink on the HTML intranet page, the link chokes because the double-address it is given as a single address does not work.

What I was told when I asked about this before in one of the Access forums was that I needed to "Have a look at the HyperlinkPart method.", and I was given a link to an article from Microsoft on this subject. I've looked over this article, and I cannot see how to apply it to my situation. I need to write a query that will only pull the address part of the information stored in this field, but have never heard of querying a method on a field from a database. Can anyone help me out with this issue?

Cheryl dc Kern

 

[Sheco]

I assume you have some ASP that is reading the database and using it to prepare some HTML that is viewed in a browser?

If so, please post a sample database field value to help visualize the data issue.

Also please do "View Source" in your web browser and find the relevant problem area of the HTML.

 

[cdck]

The select statement on the .asp page is as follows:

listSQL= "SELECT drDoc, drName, drDocType, drRev, drFile, drFileType FROM qryDocRev WHERE NewDoc = Yes ORDER BY drDoc;"
(The field in question is drFile.)

The field is then processed as such:

cldfile = rstemp(4)

IF ISNULL(cldfile) THEN cldFile= " " END IF

cldFile = replace(cldfile,"\\ad2\quality", "..")
cldFile = replace(cldfile,"\\Ad2\quality", "..")
cldFile = replace(cldfile, "#", "")
cldfile = replace(cldFile, "..", "")
cldfile = replace(cldFile, "\", "/")
The above processing is to take the directory tree format of the path from the database and turn it into the format needed for the a website

IF len(cldfile) > 1 THEN cldfile = "<a href=" & cldfile & " target="& cldDoc & ">" ELSE cldFile = " " END IF
IF len(cldFile) > 1 THEN cldFileEnd = "</a>" ELSE cldFileEnd = " " END IF

cldFileType = rstemp(5)
IF ISNULL(cldFileType) THEN cldFileType = "Not Available" END IF

cldFileType = rstemp(5)
IF ISNULL(cldFileType) THEN cldFileType = "Not Available" END IF

response.write"<TR valign=top><TD>" & cldFile & cldFileType & cldFileEnd & "</TD></TR><TR><TD colspan=5 bgcolor=DDDDDD></TD></TR>"



The following shows two rows pulled from the database, one having the hyperlink info input before the upgrade, the second having it input after:

<TR valign=top><TD><a href=/Quality_Documents/Forms/800-0553.doc target=800-0553>Word</a></TD></TR>
<TR valign=top><TD><a href=/Quality_Documents/Forms/800-0888.pdf/Quality_Documents/Forms/800-0888.pdf target=800-0888>MIS/Scan</a></TD></TR>

Cheryl dc Kern