Problem Changing Hyperlink to Text

[kieranplatt1]

I have been storing email addresses as a hyperlink in a table, but Access will not sort this into order. So as I do not need the hyperlink I changed the data type back to text but now all the email addresses appear like this format...
abc@drabc.freeserve.co.uk#

http://abc@drabc

Is there any easy way of getting rid of the # and everything after it on 30000 records? Can anyone help please?

 

[willir]

kieranplatt1

I did not completely test this, but I was able to assign the hyperlink to a text string.

This suggests you could use a loop to read through the table and assign the link to a text field. You would of course have to define the new field for the table.

Something along the lines of...

Dim rst as DAO.RecordSet, dbs as DAO.Database

Set dbs = currentdb()
set rst = dbs.OpenRecordSet("YourTableName")

with rst
    .movefirst
    while not .eof
        .edit
        !YourNewTextField = !YourHyperLinkField
        .update
        .movenext
    loop
end with

rst.close
dbs.close

Richard

 

[willir]

I must have fodder for brains.

You should be able to use an update query too...

UPDATE YourTable SET YourNewTextField = YourHyperLinkField;

A lot faster, and since this is a one shot deal, no need to save any code.