Solved

Change/append hyperlink field to text

Posted on 2013-06-10
4
493 Views
Last Modified: 2013-06-11
I have an excel file that I import into access. I have created a temp table to only pull the info I need. The issue I am having is the excel has a hyperlink that I need to use to link to another table (it's a primary key). Since you can't use a hyperling as a link to another table I tried to append the data into another table where the hyperlink would be text. I doing this it listed the text I needed but also the hyperling address. I need to be able to link to this PK.
0
Comment
Question by:DCUnited
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39235020
You might try the PlainText() function to strip off the html from the hyperlink text.  

I don't know if that will work (don't use the hyperlink data type for just this reason), but it does work for stripping off other HTML and other Rich Text markups
0
 

Author Comment

by:DCUnited
ID: 39235615
Unfortunatly the data belongs to another group and they want reporting done around it. Is there a way I can delete all texted after the #http? This is another reason for the temp table.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 300 total points
ID: 39235653
Did you try using the PlainText() function in a query?

SELECT PlainText([hyperlinkFieldName]) as URL
FROM yourTable

IF that doesn't do it, how about:

Left(PlainText([hyperlinkfieldname]), instr(plaintext([hyperlinkfieldname]), "#") - 1)
0
 

Author Closing Comment

by:DCUnited
ID: 39237488
I had already tried the
SELECT PlainText([hyperlinkFieldName]) as URL
FROM yourTable

And it didn't work, but the Left(PlainText([hyperlinkfieldname]), instr(plaintext([hyperlinkfieldname]), "#") - 1)  worked like a charm.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question