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?
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.
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.
ASKER
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)
(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)
ASKER
Nic:
Hey Thanks! I didn't know this about the memo field. Hoping to take a look at it Monday, let you know then...
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
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
ASKER
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.... .......
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
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
ASKER
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.
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
ASKER
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?
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?
ASKER
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?
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?
ASKER
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?
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)
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
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
ASKER
dovholuk: This was the problem, the single quotes. Thanks!
Nic: Thanks for your help also!
Nic: Thanks for your help also!
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)