CODEWITHSUNDEEP
ms-accesssql-likeregexp-like
user3499098
user3499098

Reputation: 1

How do i remove a "#" sign from a ms-access hyperlink field?

In a msaccess field you see an hyperlink f.e. https://stackoverflow.com when is open the field using F2 i see #https://stackoverflow.com#. How do i remove the # using an update query? Even selection query is hard

I tried the "Like" parameter but Like "#" dus not give any hits. I tried also ""#"" and "'#'" and ''#'' but no succes whatsoever.

no errors but also not the wanted result

Upvotes: 0

Views: 923

Answers (2)

IanS
IanS

Reputation: 101

A hash sign # in a text string in MS Access can be found in a search (and from there replaced) by wrapping it in square brackets, i.e. [#]

Upvotes: 0

Lee Mac
Lee Mac

Reputation: 16025

You can select records containing the hash/pound symbol (#) using the like pattern *[#]* e.g.:

select * from YourTable where YourTable.YourField like "*[#]*"

You can remove the hash/pound symbol (#) from the values held by such records using the replace function, e.g.:

update YourTable 
set YourTable.YourField = Replace(YourTable.YourField,"#","")
where YourTable.YourField like "*[#]*"

To remove the symbol from only the ends of the string, since URLs cannot contain spaces, you could use the following:

update YourTable
set YourTable.YourField = Trim(Replace(Replace(" " & YourTable.YourField & " "," #",""),"# ",""))
where YourTable.YourField like "[#]*" or YourTable.YourField like "*[#]"

Upvotes: 1

Related Questions