Copying hyperlink display into new column

[pmontalt]

Hello,

I have a table called ComponentIndex with about 3000 entries. Each entry has an ID field and data in a hyperlink field called MSDSlink.

For each entry, I want to copy just the address part of the hyperlink to a new text column, let's call it MSDSAddress. This way, I only have the URL and no displaytext in that new column.



How would I go about doing so?

Thank you!!

[ItsMe]

Why did you format as Hyperlink at the table level?

[pmontalt]

It's old data that someone else designed. I migrated over to SQL and it copied the hyperlink field as a text field, so now all I am left with is a field where all of the data is www.google.com#www.google.com#, www.accessforums.com#www.accessforums.com# etc.

I still have an old copy of the access database tables before they were converted.

[ItsMe]

OK, in a copy of the copy of Access, try converting the field to Text or Short Text or whatever basic text is called nowadays. Go into design view of the Access table and change the data type of the problem field. It should truncate the Hyperlink type to Text.

Then run an UPDATE query on your SQL table.

If you have special ALT text that you want to display then you won't want to convert the original field and will want to parse the data into two separate text fields, one for description and one for the link address.

[pmontalt]

OK, in a copy of the copy of Access, try converting the field to Text or Short Text or whatever basic text is called nowadays. Go into design view of the Access table and change the data type of the problem field. It should truncate the Hyperlink type to Text.

Then run an UPDATE query on your SQL table.

If you have special ALT text that you want to display then you won't want to convert the original field and will want to parse the data into two separate text fields, one for description and one for the link address.

Thanks, I just tried going into design view and changing the Hyperlink field to Text and Short Text. It didn't work - now I have a text field again with data like www.google.com#google.com# still, instead of just www.google.com

Any other suggestions?

[ItsMe]

You will need to use an expression like this to parse the data you want to save.
Left([TableName].[MyField],InStr([TableName].[MyField],"#")-1)

You will want to backup your DB and create two queries. One query will be a SELECT query with an Alias and the other will be an Update query.

Create a copy of your database
In the copy, create a copy of your table. Make sure the hyperlink field is of text type. You will update this table via your Update query later.
Create a new SELECT query based on the original table.
Add the PK field and an alias. The alias will be something like
MyRevision: Left([Table3].[MyField],InStr([Table3].[MyField],"#")-1)

You now have two fields in your select query, save it.
Create a new query.
Add the Copy of Table and the SELECT query to the design and join on the PK.
Add the field name (from Copy of Table) that has the text with hyperlink mumbo jumbo
Select the Update (within the Ribbon) option for your query object.
Add the Field name of your alias from the SELECT query in the Update To field within the grid.
[Query1].[MyRevision]

Save your query and Run it.

Your Copy of Table should now have the text you wish.