Pound Signs and Hyperlinks

[Williams]

I need to append data from a hyperlink field into an SQL table that is linked
into my access database. When the data gets appended into the SQL table it
has the pound signs. I understand the purpose of the pound signs is to
separate the display text # filename path # location in file. But I do not
need anything except the filename and path.

How can I extract just this data & append it to the SQL table. In most
cases the record just has the path & filename with a pound sign at the
beginning and end of the string. I can not issue a find and replace because
when I search for "#" it will stop on any number, not just the actual pound
sign.

Please help!

 

[Douglas J. Steele]

If all you need to do is remove the first and last characters, try

Mid(Left([YourFieldName], Len([YourFieldName]) - 1), 2)

If sometimes there's stuff before and after, try:

Mid(Left([YourFieldName], InStrRev([YourFieldName], "#") - 1),
InStr(Left([YourFieldName], InStrRev([YourFieldName]) - 1), "#") + 1)