Link to home
Start Free TrialLog in
Avatar of alicelknight
alicelknight

asked on

Illegal characters

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?
Avatar of nico5038
nico5038
Flag of Netherlands image

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)
Avatar of alicelknight
alicelknight

ASKER

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.
In SQL, to save isn't I would do this: isn''t ie. insert another character like the one I want to save.
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)
Nic:
Hey Thanks!  I didn't know this about the memo field.  Hoping to take a look at it Monday, let you know then...
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
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...........
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
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.
  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
ASKER CERTIFIED SOLUTION
Avatar of dovholuk
dovholuk

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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?
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?
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?
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)
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
dovholuk: This was the problem, the single quotes.   Thanks!

Nic: Thanks for your help also!