data manuipluation, vba help

[james_halliwell]

Hi All,

After some help, i have a text box that i get a user to fill in some criteria for the sql to run

Private Sub Text192_AfterUpdate()
Dim LResponse As Integer
LResponse = MsgBox("WARNING: this will affect the Import, do you want to change it ? ", vbYesNo, "Continue")
If LResponse = vbYes Then
Dim MySel As String
MySel = Me.Text192
CurrentDb.Execute "INSERT INTO TblSQLCriteria([Process], [Field], [Criteria]) VALUES('WorkOrder', 'WOPlants',  '" & MySel & "' )"
Else:
Me.Undo
Me.Repaint
Exit Sub
End If
End Sub

this works fine but what i need it the user to enter data like 'xxABC123','xxEFG123'

i get and error i think its beacuse of the ' in the text,

can anyone help


The data has to be entered like that as it fills the gaps in an sql that runs against an oracle database,

or is there a way to manipulate the data from ABC123,EFG123 to 'xxABC123','xxEFG123' (i need to add xx before the text)

- problem is there could be up to 4 or 5 different ones

ie some one could enter ABC123,EFG,123
Some one else could enter ZXY987,VBN123,ASD345,RTY678

 

[Uncle Gizmo]

It's sort of resembles an SQL injection attack. Thats whats breaking your code, your SQL statement is using the single quotation marks you passing through as if they were part of the SQL statement so the text you are passing through as you think is an SQL instruction is being broken up into something else by the single quotation marks.

 

[Uncle Gizmo]

Right, I couldn't remember the rules off hand so I looked it up, this page is Excellent:-

http://www.techrepublic.com/blog/microsoft-office/5-rules-for-embedding-strings-in-vba-code/

In your case you are using single quotes as text delimiters 'WorkOrder' so if you send a string through that has an apostrophe in it 'xxABC123' then it breaks, although I can't qiute se why, because I thought you'd be ok with 'xxABC123' as it would become ''xxABC123'' don't know though without running up some code and checking.

Reading from the above I think you should try:- "WorkOrder" instead of 'WorkOrder'..

I think that will sort it out if you ONLY pass (') but if you pass a (") it will crash again.

 

[Uncle Gizmo]

This seems a bit conflicting:-

http://bytes.com/topic/access/insights/575414-quotes-double-quotes-where-when-use-them

Extract:-
A string in VBA requires the Double-Quote (") to delimit it. Single-Quotes (') won't work.

But your code appears to be working?

 

[Uncle Gizmo]

Heres more from Allen Browne:-

http://www.everythingaccess.com/tutorials.asp?ID=Quotation-marks-within-quotes

Extract:- (The bit that applies in your case)
The single-quote character can be used in some contexts for quotes within quotes. However, we do not recommend that approach: it fails as soon as a name contains an apostrophe (like the CompanyName example above.)

 

[james_halliwell]

Mate thats great many thanks, i can use this, using " instead of '
the run a replace " with ' to get me desirded format,

greatly apperciate your hand helpfull links

 

[vbaInet]

http://www.techrepublic.com/blog/microsoft-office/5-rules-for-embedding-strings-in-vba-code/

I'm not in complete favour of this article. Although the content is spot-on but her choice of words isn't. Two things:

1. She mentions a single quotation mark when her example is that of a double quotation mark:

"Harkins"

When you enter a single quotation mark, you must enter a second - VBA wants to see a pair of them.

... what she was trying to say there is, "When you enter one quotation mark be it a single quote or a double quote, ...etc"
2. There's no clear distinction between a single quote and a double quote, but instead she makes reference to an apostrophe. In SQL or programming context it's a single or a double quote for literal strings, not an apostrophe or double quote. An apostrophe is part of a word whereas a single quote surrounds a word/sentence.

Single and double quotes are interchangeable and you see this in other languages. The most basic rule in SQL is to enclose a single quote with a double quote (and vice versa) if you want to insert them into a field.

If you want to insert 'xxABC123','xxEFG123' with a single quote surrounding each word, you simply pad it up with double quotes in the form of Chr(34) for example. So your INSERT statement would be:

CurrentDb.Execute "INSERT INTO TblSQLCriteria([Process], [Field], [Criteria]) " & _
		       "VALUES ('WorkOrder', 'WOPlants', " & [COLOR="Blue"]Chr(34)[/COLOR] & MySel & [COLOR="blue"]Chr(34)[/COLOR] & ")"

 

[Uncle Gizmo]

My understanding of it is that you cannot have a double quote and a single quote in the same string.

 

[vbaInet]

But of course you can. Where it becomes a little difficult is when you're parsing the string in an SQL statement.
When parsing strings one has to be careful in the way that single quotes and double quotes are handled. Single or double quotes should be enclosed in double quotes if you want to parse any quote.

The problem encountered in the OP is the fact that the variable needs to be parsed but at the same time parsing single quotes. So it's either you first get the string from the variable and enclose it in double quotes before parsing or you parse it using Chr(34).

Here's another variation of parsing the SQL of the OP:

MySel = [COLOR="red"]""""[/COLOR] & Me.Text192 & [COLOR="Red"]""""[/COLOR]
CurrentDb.Execute "INSERT INTO TblSQLCriteria([Process], [Field], [Criteria]) " & _
		       "VALUES ('WorkOrder', 'WOPlants', " & MySel & ")"

 

[Uncle Gizmo]

My understanding of it is that you cannot have a double quote and a single quote in the same string.

But of course you can

I think you are wrong VB...

If the delimiters are quotation marks "sometext" you can pass an apostrophe (')

If the delimiters are apostrophes 'sometext' you can pass a quotation mark (")

However
if the delimiters are quotation marks "sometext" you cannot pass a quotation mark (")

And
if the delimiters are apostrophes 'sometext' you cannot pass an apostrophe (')

Ergo:-
You cannot have a double quote and a single quote in the same string.

 

[vbaInet]

Ok, to clear things up I'm not talking about how you pass strings to variables. I'm talking about parsing SQL strings, which is what the OP is about.

if the delimiters are apostrophes 'sometext' you cannot pass an apostrophe (')

This one is quite obvious! In VBA the compiler regards an apostrophe as the beginning of a comment. In some other languages this is possible, in fact it's commonplace.

if the delimiters are quotation marks "sometext" you cannot pass a quotation mark (")

If you're talking about passing strings to a variable or forming literal strings yes you can. What implicitly defines a string (in VBA) is double quotes (not delimeters) and if you string contains one or more double quotes you can pass it to a variable or parse it to SQL. Here are some examples:

?"""begin""end""
?"begin""end"
?"begin" & """" & "end"
?"""between"""

... the main thing is finding the right amount of double quotes to pad/escape it with.

 

[Uncle Gizmo]

Originally Posted by Uncle Gizmo<br />

<br />
if the delimiters are apostrophes 'sometext' you cannot pass an apostrophe (')

Posted by vblnet
This one is quite obvious! In VBA the compiler regards an apostrophe as the beginning of a comment.

Posted by UG
No, it's nothing to do with the apostrophe as the beginning of a comment. If you change the delimiters to double quotes you can pass a single quote (an apostrophe). You wouldn't be able to pass an apostrophe if you were correct.

In other words it's nothing to do with the apostrophe being the comment identifying character, it's to do with the characters you choose to delimit your text in the SQL statement. Your choice is either apostrophes or double quotes.

Your choice of whether to use apostrophes or double quotes will depend on whether you expect to pass through single or double quotes in your text.

It was this observation lead me to make the statement:- "you cannot pass an apostrophe and a double quote in the same string".

I would be interested to know if there is a way of passing an apostrophe and a double quote in the same string. I can't think of a way, but it would be worth knowing if there was a way.

 

[vbaInet]

No, it's nothing to do with the apostrophe as the beginning of a comment. If you change the delimiters to double quotes you can pass a single quote (an apostrophe). You wouldn't be able to pass an apostrophe if you were correct.

I think you're mixing up my words. There's a difference between passing a string to a variable and parsing a string literal to SQL. Each string parsed to a variable or SQL is resolved from left to right and in some cases right to left. If you're talking in the context of parsing string literals to SQL as values then yes you have a choice of single or double quotes as I mentioned in my firs post, they are interchangeable.

Your choice is either apostrophes or double quotes.
Your choice of whether to use apostrophes or double quotes will depend on whether you expect to pass through single or double quotes in your text.

I would be interested to know if there is a way of passing an apostrophe and a double quote in the same string. I can't think of a way, but it would be worth knowing if there was a way.

Yep, that's right, the choice depends on what is being parsed. Hence, if you allow single or double quotes as values then you have to write a function that will correctly pad/escape single/double quotes before being parsed to the SQL.

More examples:

?"begin'""end"
?"'Single quote',""double quote"
?"'Single quote',""double quote""

... the trick is getting the right sequence of

 

[Uncle Gizmo]

It is obvious from the OP question that this was about passing a string into an SQL statement, so I don't quite get your argument that it is a difference between parsing and passing.

 

[vbaInet]

I don't argue Uncle T In programming you parse SQL strings and the term simply means the resolution of a string when it's read by the compiler. So my point is the problem doesn't relate to how the string is passed but how it's parsed. It's a String within a string so it's now being passed, it's being parsed.

Anyway, I think we're confusing the poster now ha!