Web URL Hyperlinks in Access Table

Question

I have a table of links to web addresses. When I view the table the hyperlinks display perfectly. When I join the table to my main table and view the results, all my hyperlinks come out like this:

및

Which BTW, according to Google translate is Korean for ALW. But I digress.

I thought it was because I'm using the Hyperlink data type incorrectly. However, even when I changed the data type to long text, same problem just without the blue underline. 

Ultimately, I am putting the results of this query into an online database and I want the user to be able to click the url and have the default browser open and display the page. 

What do I do?

tod

Answer 1

while I do the following periodically - I am not sure I ever have had the need in regard to a hyperlink field type - but give it a try:

define the field' parameter in your query

in the upper half of query design, right click, and select 'parameters'

enter the field name, and select the field type

Answer 2

I noticed in the parameters for the query that hyperlink is not a data type. Hmm...maybe that's a clue. Anway..still have the problem.

Answer 3

indeed - just checked it and it is not.... my bad

so you see it in the table ok; and if you make a query with just the table (no join) - you see it ok?

if you make a query with the table and another table needed for the form (with join) - you see it ok?

if the form is using that query - then I would delete the current control on the form, and re-drag a new one onto the form....

Answer 4

I did some experimenting. The only time the problem occurs is when I do a left join to the hyperlink table. If I right join or 'equal join' the hyperlinks are okay. 

Answer 5

I cannot replicate that behavior in my test.

I trust you are not joining on the hyperlink field itself...... I don't think that is feasible.

Answer 6

yeah, it's weird. 

I join on two other fields. The primary table has 20,000+ records with about a dozen fields. Two of the fields together make a record unique. In the second table, for every combination of those two fields I have a hyperlink that when you click it, it opens a browser to that page. 

I left joined the first table to the second using the two fields and pulled in the field with the hyperlink. Not every combination has a web address, so the left join returns blank for those. I thought it would be rather basic. I'm done with it for the day, though. I'll post a solution if I find one.

tod

Answer 7

I found a misspelled value in one of the foreign keys of my second table. I also looked at the bloated SQL statement that Access created for my query and pared it down. The hyperlinks now appear correctly. I think the SQL query may have been constructed weirdly by Access that caused the problem. 

tod

Answer 8

Hi, todtown

According to your description, I have made a sample database to try to reproduce this issue, unfortunately, I can't. There are steps and results below:

1. create TB_Article datatable

2. create TB_ArticleLink datatable

3. create left join query

SELECT TB_Article.ArticleID, TB_Article.ArticleTitle, TB_ArticleLink.ArticleURL
FROM TB_Article left join TB_ArticleLink
on TB_Article.ArticleID=TB_ArticleLink.ArticleID
and  TB_Article.ArticleTitle=TB_ArticleLink.ArticleTitle

then get this result：

In addition could you provide your datatable structure, that will help us reproduce and resolve your issue.

Thanks for your understanding.

Answer 9

I was able to resolve the issue indirectly by cleaning up my query in SQL. In the query I joined the two tables and then pulled in almost all of the fields from the first table and only the hyperlink field from the second table. I viewed the results and my hyperlinks were displaying wrong

After a bit of head scratching I decided to just write the query in SQL, then look at the results in the query builder, sort of the opposite of what you'd normally do in Access. The query builder showed all my fields as Expression and not Grouped, and when I viewed the dataset the hyperlinks were correct. So..I guess it was grouping everything was the problem. 

tod