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!
TheBaronessAsked:
Who is Participating?
 
jefftwilleyCommented:
ok...actually that's cool.

instead of trying to output to a 4th field like you are....let's open up the second one and insert test into it using your newly developed cursor positioning abilities.

example:
open memo field. set cursor at position 1. insert contents from text field 1. then go to the end of the memo field, set your cursor position and instert text field 2. close.

--------------------------------start of memo field------------------------------------------
textfield1textfield1textfield1textfield1textfield1textfield1textfield1textfield1textfield1

memofieldmemofieldmemofieldmemofieldmemofieldmemofieldmemofieldmemofield
memofieldmemofieldmemofieldmemofieldmemofieldmemofieldmemofieldmemofield
memofieldmemofieldmemofieldmemofieldmemofieldmemofieldmemofieldmemofield

textfield2textfield2textfield2textfield2textfield2textfield2textfield2textfield2textfield2
----------------------------------End of memo field-----------------------------------------

do you know how to open a recordset?
0
 
GRayLCommented:
<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?
0
 
jefftwilleyCommented:
Is it your thought to remove those characters? or find a method to get the data into Access somehow without them?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TheBaronessAuthor Commented:
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
0
 
TheBaronessAuthor Commented:
>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 brr&#696;&#696;"

0
 
jefftwilleyCommented:
Is the data in each of the fields you're concatenating ok before you put them together?
0
 
jefftwilleyCommented:
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
0
 
TheBaronessAuthor Commented:
The data is fine before its concatenated. Defining the string didn't make any difference.
0
 
jefftwilleyCommented:
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
0
 
TheBaronessAuthor Commented:
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.
0
 
jefftwilleyCommented:
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
0
 
GRayLCommented:
>about 50 lbs here<  What does it weigh there!  -  Reminds me of the blonde joke "But you are on the other side!"
0
 
jefftwilleyCommented:
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
0
 
GRayLCommented:
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!
0
 
GRayLCommented:
mybadtyping - et toi?
0
 
jefftwilleyCommented:
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
0
 
TheBaronessAuthor Commented:
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.
0
 
GRayLCommented:
TheBaroness:  While looking at questions in EE, I came across this.  Its solution may solve your problem.

http://www.experts-exchange.com/Databases/MS_Access/Q_22042737.html#17837038
0
 
TheBaronessAuthor Commented:
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)
0
 
jefftwilleyCommented:
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
0
 
TheBaronessAuthor Commented:
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.
0
 
jefftwilleyCommented:
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
0
 
TheBaronessAuthor Commented:
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.
0
 
jefftwilleyCommented:
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
0
 
TheBaronessAuthor Commented:
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?
0
 
jefftwilleyCommented:
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
0
 
TheBaronessAuthor Commented:
nope, that's not doing it either ... any other suggestions? lol
0
 
jefftwilleyCommented:
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?
0
 
TheBaronessAuthor Commented:
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).
0
 
TheBaronessAuthor Commented:
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
0
 
jefftwilleyCommented:
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
0
 
TheBaronessAuthor Commented:
>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.
0
 
jefftwilleyCommented:
hehe
0
 
TheBaronessAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.