Non-Breaking Hyphens (1 Viewer)

[grahma_aiken]

I have a memo field in a table and the string is often copied and pasted into this field from a word doc, pdf or email. It's a very specific string and it is critical that it be correct in the table as it appears on documents that become public record, hence the copy/paste.

The problem is non-breaking hyphens. I need to be able to search for and replace non-breaking hyphens in this field. Some of the records we make public are done so through online forms and when these non-breaking hyphens are copied out of the database and into the online form, the result is the html code shown and not the character.

As in

Pay to the order of Such and Such#&8209;Else

This is bad.

I've tried to get the char with code function in excel, which returns 63. 63 it ain't. I've looked for the dec char online. Either I haven't found it or I failed utterly to understand what I was looking for. I don't know how, if it is possible, to use the html code in access.

It is my birthday today and this is making it dreary. Please send help.

 

[grahma_aiken]

I hope the use of the PHP code window isn't confusing or misleading - it was the only way I could figure out to show how the string is literally appearing without the post showing the character for the html.

 

[CJ_London]

Assuming you are using 2007 or later, have you tried changing the memo field text format in table design to Rich Text?

 

[grahma_aiken]

Data is in MS SQL, I changed the data type to text but I won't know if that worked until someone brings the next occurrence to my attention. Plus I'm not certain that the issue is really with how the data is stored or if the issue is occurring when the string is copied and pasted. For these reasons I'm most interested in replacing the non-breaking hyphens with regular hyphens.

Thanks!!

 

[CJ_London]

The code you are showing is an html code which is what rich text uses since 2007 (previous it was rtf).

This link may help - the OP was pleased with the suggested solution:

http://stackoverflow.com/questions/7691569/no-line-break-after-a-hyphen

I think your problem may be that the non-breaking hyphen does not exist in plain text so it is not converted.

I appreciate your text is copied and pasted but can you not have a button or use the before update event to replace

#&8209

with a

-

 

[grahma_aiken]

I'm not quite following - the character appears to be no different than any other hyphen when viewed in the database. It is only after it is pasted into the online form and then saved does the html code show instead. Performing a search for the html code in Access as if it is a string returns nothing. How would I go about discovering if the hyphen that I am seeing is a normal hyphen or a non-breaking hyphen before copying? That is my entire problem.

 

[CJ_London]

What system are you using to view the database - if it is html based I would expect it will interpret the code correctly.

Have you tried copying the field to notepad to see what you get?

 

[grahma_aiken]

Database is MS Access 2007 and MS Access 2010 but again, the problem is not that the character appears incorrectly when viewed in Access. It appears as simply a hyphen. When copied into notepad it appears as a square although I don't see how that is helpful unless you are suggesting that I copy and paste each record individually into notepad and view it??

I need to know how to find and replace non-breaking hyphens in my tables, either through SQL Server Management Studio or through MS Access 2010. Please.

 

[CJ_London]

Since the Replace function isn't working for you I'm struggling to come up with an alternative suggestion.

The only one I can think of is to try using the plaintext function - see this link

http://msdn.microsoft.com/en-us/library/office/bb224580(v=office.12).aspx

I would expect it to be used something like:

myMemo=PlainText(myMemo)

 

[WayneRyan]

graham,

I've dealt with the non-breaking space in SQL Server; it is an ASCII 160.

I think your "hyphen" is an ASCII 151.

To see its position in SQL Server:

Select YourField, CharIndex(Char(151), YourField)
From   YourTable
Where CharIndex(Char(151), YourField) > 0

To see its position in Access:

Select YourField, InStr(1, YourField, Chr(151))
From   YourTable
Where InStr(1, YourField, Chr(151)) > 0

To Replace it in SQL Server:

Update YourTable
Set       YourField = Replace(YourField, Char(151), '-')

in Access:

Update YourTable
Set       YourField = Replace(YourField, Chr(151), '-')

hth,
Wayne