Solved

ASCII Characters replacing text in field

Posted on 2006-10-31
34
452 Views
Last Modified: 2010-07-27
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!
0
Comment
Question by:TheBaroness
  • 15
  • 14
  • 5
34 Comments
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
<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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Is it your thought to remove those characters? or find a method to get the data into Access somehow without them?
0
 

Author Comment

by:TheBaroness
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
>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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Is the data in each of the fields you're concatenating ok before you put them together?
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
The data is fine before its concatenated. Defining the string didn't make any difference.
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
>about 50 lbs here<  What does it weigh there!  -  Reminds me of the blonde joke "But you are on the other side!"
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
mybadtyping - et toi?
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
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
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:TheBaroness
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
nope, that's not doing it either ... any other suggestions? lol
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:TheBaroness
Comment Utility
>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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
hehe
0
 

Author Comment

by:TheBaroness
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now