Solved

Illegal characters

Posted on 2001-06-02
19
484 Views
Last Modified: 2013-12-03
Hi All!  I'm trying to save text from a multiline textbox to a memo field.  This text may contain "" ' , tabs or carriage returns.  Are any of these characters illegal in Access?  And if so, which ones?  I'm assuming I will have to do a search/replace routine.  Any good ideas on this?
0
Comment
Question by:alicelknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 3
19 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6149410
Moving the field content "straight away" won't give any problems.
When using the fieldvalue inside a stringed query the quote (") can damage the build of the string. However I assume you're not doing that.
So just use an update query for the move.

Nic;o)
0
 

Author Comment

by:alicelknight
ID: 6149429
nic;0)
The user pastes text from a web page into this textbox.  When they go to save it they get an error message.  I'm not at work now(thank god!) to replicate the save and see the exact error message, I'm "assuming" this is due to maybe a quote or ' in the field.  When I save it, I save the value of the textbox.  I'm new to Access, used SQL up until now.
0
 

Author Comment

by:alicelknight
ID: 6149435
In SQL, to save isn't I would do this: isn''t ie. insert another character like the one I want to save.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 54

Expert Comment

by:nico5038
ID: 6149512
If you have a memo field on a form, all quotes should be accepted.
(I tested this with copy/pasting your first "answer" comment above)

So I would change the fieldtype in the table to memo. (The present info will cause no problem.)

If there is a problem I would sooner try to check on the Tools/References (under VBA) as the MISSING libraries there often cause trouble after installing a .mdb on another computer !

If that's OK, then let me know the exact message.

Nic;o)
0
 

Author Comment

by:alicelknight
ID: 6149520
Nic:
Hey Thanks!  I didn't know this about the memo field.  Hoping to take a look at it Monday, let you know then...
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6149594
alicelknight,

how are you saving the records? my guess is that you've got code that runs an INSERT query. is this the case?

as you noted above (and nico pointed out), apostrophes/quotes will need to be "doubled" in order to have the SQL statement work correctly if you build a string.  let us know how you are "saving" the data so we can help you better... is it through code or is the field bound?

i'm pretty sure that nico verified that a bound field will accept apostrophes/quotes, but it feels like you don't have a bound field....

let us know monday?

dovholuk
0
 

Author Comment

by:alicelknight
ID: 6149606
dovholuk:
Yes, I'm running an insert into Access.  And yes, I'm building a SQL string through code, nothing is bound.  Something like:
If .OpenRecordSet() then
       .ID = CLng(Val(Text1.Text))
       .Name = Text2.Text & vbnullstring
       .Memo = TextMemo & vbnullstring
      ...................etc...........
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6149793
well that's gotta be your problem then. you need to parse the text in your fields to be sure no extraneous 's or "'s are getting through.  

Questions:
why are you terminating each string with a null character?

do you have a routine that will "double" selected characters? i use my own routine that takes two parameters, one parameter is the string to check and one is what character to "look for". it utilizes InStr() if you are interested.

do you need help creating a String parsing routine or are  you all set from here on out?

dovholuk
0
 

Author Comment

by:alicelknight
ID: 6149832
dovholuk:
Good question! :) Seems I'm thinking in reverse.  I know if I pull from the database a NULL value I get an error without the vbnullstring.  Guess i wasn't paying attention...  
I did 2 routines myself but don't know if they'll actually work with Access.  I tested out pasting to text1, saving to 2nd textbox with MemoToDB, then saving to text3 with DBToMemo.  The text comes back correctly.  I notice on my homepage the articles with quotes start with ``This....and end with".  Here are my routines, quite simple but I've never done this before.  If you can improve them, I'm willing to increase the points.  

Private Function MemoToDB(sText As String) As String
    Dim strToDB As String
   
    strToDB = Replace(sText, """", "`")
    strToDB = Replace(strToDB, "'", "''")
    MemoToDB = strToDB
End Function

Private Function DBToMemo(sText As String) As String
    Dim strFromDB As String
   
    strFromDB = ReplaceMulti(sText, vbTextCompare, "''", "'", "``", "`", "`", """")
    DBToMemo = strFromDB
End Function

I've tried to make the MemoToDB one function but could only get correct results using 2 replace()'s.
0
 

Author Comment

by:alicelknight
ID: 6149835
  strToDB = Replace(sText, """", "`")
  Reads: dblquote dblquote dblquote dblquote, dblquote ` dblquote
   strToDB = Replace(strToDB, "'", "''")
   Reads: , dblquote ' dblquote, dblquote ' ' dblquote

ReplaceMulti(sText, vbTextCompare, "''", "'", "``", "`", "`", """")
   Reads: dblQ ' ' dblQ, dblQ ' dblQ, dblQ ` ` dblQ, dblQ ` dblQ, dblQ ` dblQ, dblQ dblQ dblQ dblQ
0
 
LVL 8

Accepted Solution

by:
dovholuk earned 150 total points
ID: 6149867
i use apostrophes exclusively as SQL server interprets quotes differently depending on certain settings.  with that said, here's my method:

(DA = double apostrophes)
Usage:  DA(Me!txtField1) if called from a form or DA(SomeStringVariable) if called from a module etc. etc. etc.

Function DA(Optional strCheck As Variant) As String

   Dim lngApostPos As Long

If IsMissing(strCheck) Then Exit Function
If IsNull(strCheck) Then Exit Function

If InStr(strCheck, "'") = 0 Then
   DA = strCheck
   Exit Function
End If

lngApostPos = 1

Do Until lngApostPos = 0
   lngApostPos = InStr(lngApostPos, strCheck, "'")
   If lngApostPos <> 0 Then
      strCheck = Left(strCheck, lngApostPos - 1) & "''" _
        & Right(strCheck, Len(strCheck) - lngApostPos)
      lngApostPos = lngApostPos + 2 'skips the two apostrophes...
   End If
Loop

DA = strCheck

End Function


now, whenever the user COULD enter an apostrophe via typing/cutting-pasting, always wrap your controls with a DA().  this should take care of your issues.

i don't remember if i checked this method using quotes or not, but i think i did.

let me know if you have any problems, but this should clear you up.

dovholuk
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6149871
a few explanations of the code might help...

first, the strCheck variable is declared as a variant. this isn't ideal, but it's necessary to allow NULL references.  it's too much of a pain to remember all the time to make sure a valid string is passed to the function. the variant datatype is very handy in this situation.  also, strCheck is optional don't quite remember WHY, but i think i did it for a reason. lol

this method will also parse an ENTIRE string. if there are 1000 apostrophes in the source string, you'll get 2000 apostrophes back! so you don't need to worry about how many apostrophes are in a particular string. this method will take ALL of them and double them. for example:

the following:

"this is just' a test' of the apos'trophe's that're in a s't'r'i'n'g"

returns:
"this is just'' a test'' of the apos''trophe''s that''re in a s''t''r''i''n''g"
(btw, i cut and pasted the string into the Access immediate window to get the result, i didn't do it by hand)

i think that sums it up nicely.

let me know if you need anything else

dovholuk
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6149877
ok, so i went one more step, just incase you need to double quotes. the quotation mark is chr(34) in ascii in case the below code throws you off...

here's a DQ (double quotes) method that will parse a string for quotes and double them up as well...

Function DQ(strCheck As Variant) As String

   Dim lngApostPos As Long

If IsMissing(strCheck) Then Exit Function
If IsNull(strCheck) Then Exit Function

If InStr(strCheck, Chr(34)) = 0 Then
   DQ = strCheck
   Exit Function
End If
lngApostPos = 1

Do Until lngApostPos = 0
   lngApostPos = InStr(lngApostPos, strCheck, Chr(34))
   If lngApostPos <> 0 Then
      strCheck = Left(strCheck, lngApostPos - 1) & Chr(34) & Chr(34) _
        & Right(strCheck, Len(strCheck) - lngApostPos)
      lngApostPos = lngApostPos + 2 'skips the two apostrophes...
   End If
Loop

DQ = strCheck
End Function


this could be further expanded to allow you to SPECIFY which character to replace...


here's a double character (DC) method...

Function DC(strCheck As Variant, CharacterToDouble As String) As String

   Dim lngApostPos As Long

If IsMissing(strCheck) Then Exit Function
If IsNull(strCheck) Then Exit Function

If InStr(strCheck, CharacterToDouble) = 0 Then
   DC = strCheck
   Exit Function
End If
lngApostPos = 1

Do Until lngApostPos = 0
   lngApostPos = InStr(lngApostPos, strCheck, CharacterToDouble)
   If lngApostPos <> 0 Then
      strCheck = Left(strCheck, lngApostPos - 1) _
          & CharacterToDouble & CharacterToDouble _
          & Right(strCheck, Len(strCheck) - lngApostPos)
      lngApostPos = lngApostPos + 2 'skips the two apostrophes...
   End If
Loop

DC = strCheck
End Function

take your pick of which method works for you ! :)

dovholuk
0
 

Author Comment

by:alicelknight
ID: 6149894
dovholuk:
Hey thanks!  Let me ask one more question... when I pull the memo field back out of the database(Access), how would I get the data to the way it was before inserting it into the database?  Or do I even need to do anything?
0
 

Author Comment

by:alicelknight
ID: 6149898
dovholuk:
Hey thanks!  Let me ask one more question... when I pull the memo field back out of the database(Access), how would I get the data to the way it was before inserting it into the database?  Or do I even need to do anything?
0
 

Author Comment

by:alicelknight
ID: 6149900
dovholuk:
Hey thanks!  Let me ask one more question... when I pull the memo field back out of the database(Access), how would I get the data to the way it was before inserting it into the database?  Or do I even need to do anything?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6150101
Why all the coding?
I see:
If .OpenRecordSet() then
      .ID = CLng(Val(Text1.Text))
      .Name = Text2.Text & vbnullstring
      .Memo = TextMemo & vbnullstring

This is recordset processing and the memofield will be passed as content and not as part of a string....
As an alternative you could always use a dummy table to have a bound memo field that can be inserted (also fro an SQL command based on a string) without all the quote coding.
I guess a memofield in a SQL string format will cause trouble anyway as the length can exeed the max string length.....

Nic;o)
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6150355
once you've doubled the apostrophes/quotes, the data will be in the database in the correct format. you won't need to do anything to retreive it.

the problem you were running into is when you build the SQL for the INSERT statement. that's the only place you need to worry about doubling apostrophes/quotes.  once it's in the DB, it's stored and you don't need to worry about it again.

dovholuk
0
 

Author Comment

by:alicelknight
ID: 6150528
dovholuk: This was the problem, the single quotes.   Thanks!

Nic: Thanks for your help also!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

726 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