My SQL DoCmd hiccupped when ever I get a single quote. How do I make sure no single quotes get into name fields, as well as all control characters, and potentially damaging SQL code.

Thanks,
Marc Noon

Marc Noon

Coding is a game. Live it, love it, break it, run it.

__________________
Marc Noon

Coding is a game. Live it, love it, break it, run it.

Marc,
I've had this problem before. The way I get around it is:
Dim strquote as string
strquote = """"
inside your sql statement wrap this around your text field. It will then allow single quotes within the text field.

i.e. field ComputerName Marc's Computer

sql = "insert into xxx (Computername) select " & strquote & computername & strquote & " as expr1;"

Hope this helps!

Kevin


dartcoach

Marc,

Did that work for you?

Kevin

dartcoach

I Don't under stand how it works. Is this a trick? Or is there a reason why this method is used. I have had that exact problem with control characters in sql for computer name because some admin named their computer and included a dash "-". I just don't understand how it works. I guess I need to press the little magic I believe button?

Marc Noon

Coding is a game. Live it, love it, break it, run it.

Marc,
When you're inserting a text value that has special characters, those characters could mean something to VB or Access.

i.e. a dash - is also a minus sign.
     a tic mark ' encloses text

so when you have a text field that has 1 or more of these characters, it can cause havok with your sql unless the complete text field is enclosed in double quotes. There is probably a better way to do it than the way I've done it, but mine works for me. I just make sure that everytime I'm inserting a text field, I enclose it with the double quote.

Hope this helps!

Kevin

dartcoach

Yeah I understand that... but what you showed me is not double quotes... its quadruple quotes.

Marc Noon

Coding is a game. Live it, love it, break it, run it.

Marc,
Your right, sorry about that. I can't remember if I found an example somewhere or I found it by trial and error. I thinks its because when you code strquote = """" it's actual value is "". Then that brackets your text within a single double quote. I think I'm confusing myself now! I just know that it makes it easy and it works!

Kevin

dartcoach

I think you were right to begin with...

http://www.accessmonster.com/Uwe/For...String-problem

Marc Noon

Coding is a game. Live it, love it, break it, run it.