Copy and pasting manually (Ctrl V, Ctrl C) between fields versus copy a column of fields

[chriskeeganhw]

Hi

So I have a calculated field that uses some fixed data plus some values in other fields to create what is in essence a URL.

The calculated field produces a perfect result (in that I can manually copy the URL, paste it into a browser and it works)

I then have created another field which is defined as a hyperlink field, the idea being to copy the content of the calculated field into the hyperlink field so I can click on it and go to the URL that has been created.

This works correctly as well BUT only if tab into the calculated field, do CTRL C to copy it, tab into the hyperlink field abd then do CTRL V to paste the value in. Doing this not only makes the hyperlink work but also adds things like "%20" for any spaces that are (correctly) calculated in the calculation

However there are 17k records where I need to copy the caluculated field value into the hyperlink field so it generates the correct format and syntax for the URL to open (In fact it is worth than this as each record actually contains 3 different calculated fields to generate 3 different URLs for the same record, so I have like 51k manual copy and paste actions to undertake)

I thought I would save time by just opening up the table view, copy the column of calculated fields, then pasting it into the column of hyperlink fields. However this doesnt actually "convert" the calculated field into a correct format, syntax and structure when manually doing CTRL C CTRL V does.

Is there anyone who can explain why this is? In my naivity and inexperience I kind of thought just copying the column and pasting it would do the same as manually copying the content of one field in one record and pasting it into another field.

Anyone got a quick solution to save me getting deforminty of my fingers pressing CTRL C CTRL V 100,000 times

Many thanks for any help

(PS Happy to do the manual copy and paste thing as I add more records but a quicker/neater solution, even at the table level, is fine as I am not a very good programmer - or even a programmer at all)

[Gicu]

You could try to use Application.FollowHyperlink on the original calculated field and see if it works, that way the second field is no longer needed.
https://learn.microsoft.com/en-us/of...ollowhyperlink
You can call it from a separate button or better from the double-click event of the calculated field control (textbox).
Cheers,

[Micron]

I just tested to see if I concatenated a url in an unbound textbox and set the IsHyperlink to true to test if that would work. It does, and would also eliminate the second field. You seem to have the url calculated, so why not use that? While I got a formatted hyperlink, it wasn't blue, but that probably could be fixed via font colour. Don't know if that's because it was unbound (so not picking up the hyperlink format from a table field) or not.

[chriskeeganhw]

I just tested to see if I concatenated a url in an unbound textbox and set the IsHyperlink to true to test if that would work. It does, and would also eliminate the second field. You seem to have the url calculated, so why not use that? While I got a formatted hyperlink, it wasn't blue, but that probably could be fixed via font colour. Don't know if that's because it was unbound (so not picking up the hyperlink format from a table field) or not.

Hi Many thanks for your response.

I missed out an important bit of information in my post ....

The URL isnt used within Access.

WHat I actually need to do is to create a working and correctly formatted URL as a field in its own right becuase ultimately I export the fileds into a txt/csv file which I then import into a non windows program where it gets imported and the user can click on the hyperlink (its a training app where the user can see a whole range of training videos that they select from a list, but the "app" is very simple and its maxmimum functionality is to simply open a URL, hence thats why I have to make the URL work as a correctly formatted URL with hash marks, %20 for a space etc). The action of manually doing CTRL C and CTRL V within access seems to create the needed URL syntax and hashes and spaces which when exported into a text file and then imported into the app seems to work just as needed)

Thanks

[Micron]

Further testing; I have this in one field - #https://www. and this in another field - google.ca
The textbox FollowHyperlink property is true and the control source is an expression that concatenates the 2 fields. It works. You might want to show some examples of your fields data if you get stuck. F'rinstance, you might not have "#" at the beginning, which would be easy to fix with an update query.

[Micron]

I posted before seeing your reply. It would seem that to avoid copy/paste into a field, all you'd need is an update query to concatenate the parts into a table field that is formatted as hyperlink? Maybe post some examples of the component parts?

[Gicu]

An alternative to Micron's update query suggestion is to create a new table to be used for exporting to csv\text in which you set up a field as hyperlink. To export run a delete query first to empty this new table (DELETE * from tmpExportURL) then run an append query to populate it from your original table making sure you append the calculated field into the hyperlink one.