Solved

Illegal characters

Posted on 2001-06-02
19
443 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
  • 10
  • 6
  • 3
19 Comments
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
Comment Utility
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
Comment Utility
In SQL, to save isn't I would do this: isn''t ie. insert another character like the one I want to save.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:alicelknight
Comment Utility
  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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
dovholuk: This was the problem, the single quotes.   Thanks!

Nic: Thanks for your help also!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 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

9 Experts available now in Live!

Get 1:1 Help Now