Using Special Characters e.g. " and |

[Ian Belcher]

Hi,
I've got a text field which I have no control over what data is entered.
I'm also not allowed to change the data (internal auditors!). When I want to
use the data I get problems with the string finishing at " and not at the
end. Is there a way to get around this problem? I also get errors with |.
I assume there are several characters that will interupt a string. Does
anyone have a complete list?

Thanks
Ian

 

[Dirk Goldgar]

Hi,
I've got a text field which I have no control over what data is
entered.
I'm also not allowed to change the data (internal auditors!). When I
want to use the data I get problems with the string finishing at "
and not at the end. Is there a way to get around this problem? I
also get errors with |. I assume there are several characters that
will interupt a string. Does anyone have a complete list?

I'm not aware of any problems with the pipe character (|), provided it's
inside quotes. Maybe you could give an example of the sort of problems
you're encountering with that one -- data and code, please. Embedded
quotes do cause problems when you try to embed them into SQL statements
you build at run time. Usually, your best bet is to pick whichever is
the least common of the two accepted quote characters, (') and ("), and
use that as the string delimiter. Then use the Replace function to
double-up any occurrence of that character that may occur inside the
string.

I usually settle on the double-quote character ("), since the
single-quote (') is more common in the data I see, so I've written this
function to do my quoting for me:

'----- start of code -----
Function fncQuoted(StringToQuote As String) As String

fncQuoted = _
Chr(34) & _
Replace(StringToQuote, Chr(34), """""", , , vbBinaryCompare) & _
Chr(34)

End Function
'----- end of code -----

I can call the function when building a SQL string, without worrying
about what a string variable contains. For example,

strSQL = _
"INSERT INTO tblCustomerContacts " & _
"(CustomerID, Employee, ContactDate, ContactDetails) " & _
"VALUES(" & _
Me.CustomerID & ", " & _
fncQuoted(Me.txtEmployee) & ", " & _
Format(Me.txtContactDate, "\#mm/dd/yyyy\#") & ", " & _
fncQuoted(Me.txtContactDetails) & _
")"

 

[Ian Belcher]

Thanks for the function Dirk. Do you mind if I use it? I don't want to the
get the company that I work for in trouble etc, so if you would like a line
of recognition or something similar can you let me know?

The error I get with the pipe character is:
Syntax error in string in query expression '[ValidationCode]="Pupil should
be full timeAlexOuthwait|e17/04/1999"'

I get a similar error when there is an " in the text field so I'm assuming
it's a similar problem.

The field that has the problem is:

DLookUp("[ValidationCode]","[x-RemoveValidationTable]","[ValidationCode]=" &
Chr$(34) & [ValidationCode] & Chr$(34))

Thanks again
Ian

 

[Dirk Goldgar]

Thanks for the function Dirk. Do you mind if I use it? I don't want
to the get the company that I work for in trouble etc, so if you
would like a line of recognition or something similar can you let me
know?

Sure, go ahead and use it. No attribution is necessary on that trivial
bit of code. I probably copied the idea from someone else, anyway.

The error I get with the pipe character is:
Syntax error in string in query expression '[ValidationCode]="Pupil
should be full timeAlexOuthwait|e17/04/1999"'

I get a similar error when there is an " in the text field so I'm
assuming it's a similar problem.

The field that has the problem is:

DLookUp("[ValidationCode]","[x-RemoveValidationTable]","[ValidationCode]
="
& Chr$(34) & [ValidationCode] & Chr$(34))

Thanks again
Ian

I don't see anything inherent in the pipe character or in the DLookup
expression that should give an error unless the [ValidationCode] control
contains the double-quote character. Try it like this, after pasting
fncQuoted into a standard module, and see what happens:

DLookUp("[ValidationCode]","[x-RemoveValidationTable]",
"[ValidationCode]=" & fncQuoted([ValidationCode]))

Note: I broke that onto two lines for posting, but you'll probably need
to put it back onto one line before running it.

If you still get that error message, could it be the result of some
other query? Is [x-RemoveValidationTable] a query that refers to the
[ValidationCode] control?

 

[Fred Boer]

Dear Dirk:

Sure, go ahead and use it. No attribution is necessary on that trivial
bit of code. I probably copied the idea from someone else, anyway.

Trivial for you, maybe! But that code (which I've also snared from you once
upon a time..) is perhaps the most treasured function in my collection; when
I think of all those hours I spend tearing what little hair I could find out
of my head, in continual exasperation as I wrestled with apostrophes, and
quotes in my strings... <shudder>... Then along came that lovely little
function... ahhh....and the birdies began to sing, and flowers began to
bloom, and the sun broke through the clouds...and I was able to dance
happily about knowing that I never had to worry about quotes in strings
again...

Cheers!
Fred Boer

 

[Jeff Conrad]

Open a window Fred, I think you may need some fresh air.
<g, d, & rrrrrrrr>

 

[Jeff Conrad]

LOL!!
Lots of room for you anytime Fred.

 

[Fred Boer]

Maybe you're right, Jeff! Is there still room up there around Bend if I want
to come get some fresh air up there?

<g,d,rrr... myself!>