SQL Text Variable that includes quotes 1

[cranebill]

Ok here is the problem... I am using a part (part as in a product we have) as a variable. The part is as follows:

L 3" x 5" x 3/8"

It is a piece of steel.

I used the following syntax:

Set rst = dbs.OpenRecordset("SELECT WtPerIn from MiscSteelWeights Where MiscSteelWeights.MiscSteel = """ & GSAPart & """&quot

This Syntax works with other variables as text but with this variable having quotes within itself it is generating this error.

Run-time error '3075':

Syntax error (missing operator) in query expression 'MiscSteelWeights.MiscSteel = "L 3"x 5" x 3/8""'.

Is there a way to get around this or am i going to have to remove the inch symbol from all these parts.

Any Help Would be Appreciated

Bill

 

[swampBoogie]

Set rst = dbs.OpenRecordset("SELECT WtPerIn from MiscSteelWeights Where MiscSteelWeights.MiscSteel = '" & replace(GSAPart,"'","''") & "'" )

 

[cranebill]

What does this replace function do... Never heard of it

 

[swampBoogie]

In SQL, if you wish to include a ' in a string you should use ' as an escape. Thus

select * from t where c = ''''

will find those records where the column c contains one '. By using the replace you will ensure that the sql statement will not break if the input contains one or more '.

Using " as a string delimiter is not in accordance with the SQL standard.

 

[danjames]

Not sure if I should post a separate Question but here goes.

In addition to the quotes described above are there any other characters that screw up SQL queries assembled in this way.

I'm thinking of writing a function to strip SQL 'illegal' characters out of a string unless someone knows of some feature of Access 2k that can achieve the same aim.



Dan James

 

[TonyJollans]

Hi Bill,

The problem you have is to do with the strings containing the [red]"[/red] characters being interpreted twice - once by VBA and once by Jet. As you are obviously aware, delimiter characters inside strings must be doubled up. The trouble here is that you want to use one string building process to build up anoter string which contains doubled-up quotes. It's not impossible but it's a bit confusing to the human eye, and error-prone.

Fortunately, there is a way around it in this case. Jet SQL strings can be delimited with either single-quote ([red]'[/red]) or double-quote ([red]"[/red]) characters. As your string contains double-quote characters it is best to use single-quote characters to delimit them. To do this, change your code to ..

[blue]Set rst = dbs.OpenRecordset("SELECT WtPerIn from MiscSteelWeights Where MiscSteelWeights.MiscSteel = [highlight]'[/highlight]" & GSAPart & "[highlight]'[/highlight]")[/blue]

================================================================================

Replace is a function. new in VBA 6 (Access 2000), which replaces all instances of one (sub)string inside a string with a different (sub)string. It is a very useful function, but swampBoogie's use of it achieves nothing in this instance as you have no [red]'[/red] characters in your GSAPart string

danjames,

There are no other special characters that need 'cleaning'. The only issue is with using the same character inside a string as the one used to delimit it.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[cranebill]

Thanx Tony good information. I did come up with a solution to this some time ago lol... but will use the information you provided in the future. This was an old post of mine... like 8 or 9 months ago lol. You get a star anyway lol

Bill

 

[TonyJollans]

Hi Bill,

Thanks for the star. I didn't notice the dates on the posts, just answered the question

danjames,

Yes, you should usually start a new thread for a new question, but I think I've now answered you this time, so it's just to know for next time.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[danjames]

TonyJollans,

Thanks for your help on my first post here. Much appreciated.



DJ - Just plugging away, can't wait for deployment day...