Pound Signs and Hyperlinks

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!

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Williams" wrote in message
...
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!