Start Free Trial

asked on

ASCII Characters replacing text in field

I have a memo field that is a concatenation of 3 fields. The data for one of the three fields is copied and pasted from a Word document. Most of the time this works fine, but on a few occasions the last bit of text in the concatenated field is replaced by odd ASCII characters. The characters don't show up in the original field. I thought it was because of the concatenation at first, but the field allows me to put in more than 255 characters.

Here's a sample of what's happening (I can't paste the whole thing for client confidentiality reasons):

is in the final stages of the Planning and Leadership pha(rʸʸ

Unfortunately the characters won't paste exact, but that's the general idea. Help!

<I thought it was because of the concatenation at first, but the field allows me to put in more than 255 characters.> Huh? Its a memo field, it's supposed to. It looks like some Unicode structure - which I suppose is not allowed in memo fields - or it may be that the first concatination sets the code for the remainder. Do you always concatinate the same fields in the same order? Which of the three portions has the odd stuff?

Is it your thought to remove those characters? or find a method to get the data into Access somehow without them?

ASKER

GRayL, yes, the fields are always concatenated in the same order. Its the middle portion that's giving me the problem ... the third section isn't a problem at all. Here's the code for the concatenation:

If Form_ClientBlurbForm.ClientCampaignCase = "." Then
sBlurb = [BlurbCase1] & Form_ClientBlurbForm.ClientCampaignCase & " " & [BlurbCase2]
Else
sBlurb = [BlurbCase1] & " " & Form_ClientBlurbForm.ClientCampaignCase & " " & [BlurbCase2]
End If

BlurbPreview = sBlurb

ASKER

>Is it your thought to remove those characters? or find a method to get the data into Access somehow without them?<

Well, the characters are replacing the "real" text, so I want it to show up correctly. Say the phrase should be "the quick brown fox" -- what I'm getting is "the quick brrʸʸ"

Is the data in each of the fields you're concatenating ok before you put them together?

Try defining the string

dim strCase2 as string
strCase2 = Form_ClientBlurbForm.ClientCampaignCase.value

If strCase2 = "." Then
sBlurb = [BlurbCase1] & strCase2 & " " & [BlurbCase2]
Else
sBlurb = [BlurbCase1] & " " & strCase2 & " " & [BlurbCase2]
End If

BlurbPreview = sBlurb

ASKER

The data is fine before its concatenated. Defining the string didn't make any difference.

Not to beat a dead cat....but how about this?

dim strCase2 as string
strCase2 = Form_ClientBlurbForm.ClientCampaignCase.value
strCase2 = Clean(strCase2)
If strCase2 = "." Then
sBlurb = [BlurbCase1] & strCase2 & " " & [BlurbCase2]
Else
sBlurb = [BlurbCase1] & " " & strCase2 & " " & [BlurbCase2]
End If

BlurbPreview = sBlurb

ASKER

lol ... careful there, I'm owned by three of the beasts. Anyway, where did you get the Clean function from? I'm getting an error message on it.

It is in my Access 2k help file actually...Clean_Worksheet_Function...but might apply just to Excel. I was tracing down "Trim" and used the "see also". Trim removes any erroneous spaces, but clean is supposed to remove "Special" characters.

I was also considering positioning your cursor to the right cursor position in your memo box before you started adding the third field. I hadn't gotten that far though.

Another thought was to build the string in steps rather than one statement. Like
sBlurb = [BlurbCase1]
sblurb = sblurb & [blurbcase2]
sblurb = blah blah blah...

I don't know if has to do with doing it all in one step or not....but since that isn't working...shooting for alternatives~

By the way...I have a silky black cat that weighs about 50 lbs here. So I'm animal friendly as well! Plus a Huge Monte Python fan....beating a dead cat is standard humor!!

;o)
J

>about 50 lbs here< What does it weigh there! - Reminds me of the blonde joke "But you are on the other side!"

lol Ray!!...If I knew how to convert metrics, I'd take a stab at telling you how many grams THERE! In what part of Canookia do you live? I've been to Calgary, Edmonton, Banff.
J

J: Way further east. I'm a displaced westerner (Red Deer, AB) living in the YUL environs - if you know your airport codes - est toi!

mybadtyping - et toi?

I'm a lot further south...I'm down in Tejas, Houston area. Rasied here...did my 10 yr Air Force stint in Hawaii, Married a Canook stripper that I met there, did some traveling with her to see her family a few times. She's my Ex now, so I haven't had a reason to go back. Can't say I like 40 below much anyway...so I stay way south.
J

ASKER

One of my beasts is black too, but all three of them combined don't weigh 50 lbs! You sure you don't have a jaguar there? lol

Well, doing the concatenation in phases didn't work. Clean would be great but it doesn't appearl to be an Access function.

TheBaroness: While looking at questions in EE, I came across this. Its solution may solve your problem.

https://www.experts-exchange.com/questions/22042737/copying-data-from-one-cell-into-another.html#17837038

ASKER

GrayL ... is there a way to make that work without exporting to Excel though? I'm joining the data in a fourth field on the form (I know, not practical, but its what the user wants ... sigh)

TB,
Here's another out of an EE q that may help...to run your string through before you concatenate...

Here is SQL
SELECT tblSpecial.T, tblSpecial.Z, EvalChar([Z]) AS Expr1
FROM tblSpecial;
Modify with your table fields.....
my field "Z" is one with special characters

Paste and save this into a module:

Function EvalChar(z As Variant)

Dim counter As Integer
Dim OK As Boolean
Dim ASCValue As Integer
Dim MyStr As String

For counter = 1 To Len(z)
ASCValue = Asc(Mid(z, counter, 1))
OK = True

Select Case ASCValue
Case 9, 10, 13
Case 32 To 126
Case 145, 146
Case Is > 160
Case Else
OK = False
End Select
If OK Then

MyStr = MyStr & Mid(z, counter, 1) & " "
End If
Next

EvalChar = MyStr

End Function

ASKER

That makes it even more interesting! The function didn't solve the problem, but I was able to examine the text character by character. The portion that's showing as ASCII characters on the screen is being processed as their correct characters, so its not the actual text that's the problem. The down side is, I could live with funky characters on the screen, but the field is being printed and exported that way too.

This is getting interesting....is there a pattern then? like it's always the characters at the END of the second string getting mussed up? I'm thinking if you insert a VB line feed at the end, might circumvent any oddness....but you're running me out of ideas!! lol

ASKER

That's part of the problem ... there's NO pattern. It happens in maybe 3 of 1000+ records ... no rhyme or reason to the number of characters in the field. The only thing consistant is when it happens, it happens at the end of the second section ... there will be some garbled characters, then section 3 shows up fine. I even wondered if I was bringing over some odd characters from Word by pasting the text, but when I "straight typed" it, the same thing happened.

There's always forced text blocking. See if you think it's worth the trouble

Say for every 100 characters len(mystring) = 100 then mystr & VbCrLf .

OR

Set the cursor position at specific intervals say after you write your first string, set position at the end + 1 then write your second string, then do the same there.

Do you think it could be a buffer problem.

Grasping here

ASKER

VbCrLf won't work ... this field will be used in Word documents and I want the users to just be able to cut and paste (which is why I want to get rid of these stupid characters in the first place lol) ... but how would I go about setting the cursor position?

something like this will go to the end of the string, add one space and let you do whatever

f = Forms!Myform
strIn is your string
f(strIn).SelStart = Len(f(strIn).Value) + 1

ASKER

nope, that's not doing it either ... any other suggestions? lol

Just to make sure....you're taking the contents out of 3 text boxes, or "text" defined fields in a table, and putting them all into one Memo field....is that correct?

ASKER

no, the first part is a text field, the second is a memo field and the third is a text field, combined in a memo field (you almost had it lol).

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Yes (about the recordset) ... so I want to run through the recordset on this, instead of having it update on the current record (as it is now)? Also, how would I set the cursor at position 1? I've got the end of the field part, but position 1 has me a little baffled

ummmm

f(strIn).SelStart = 1

:o)

If you've another way...outside of a recordset, to open the memo field then do it that way...I'm just a recordset freak, that's all

ASKER

>f(strIn).SelStart = 1<

Duh ... sometimes the obvious eludes me lol. I love recordsets myself and I use one to do a mass update, but I also need a single record to update On Current. So let me play with this and I'll let you know how it goes.

hehe

ASKER

So I actually haven't had a chance to play with constructing the field, but I got really pressed for time and discovered a quick and dirty way to fix the three records that were making me crazy. But I wanted you to get the points because you were so much help (and so patient!) Greetings to the 50 lb feline lol, and thanks for all your help.