Apostrophies SQL from Access

[marcbell]

Good afternoon,

The below query works when the string value has no apostriphies e.g
jack wines, however it crashes over when it's Jack's wines.
I had a play about with speech marks and apostrophies and I just can't
get it right. I'm sure theres a quick fix, but i can't see it.
Many thanks in advance,

Marc



Sub get_cust_all()

Path = "D:\Documents and Settings\Marc\My Documents\testing.mdb"

'This part is the problem as its the apostrophies
what_comp = "'" & frm_invoice.cmb_custname.Text & "'"

Set db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)

Set qry = db.CreateQueryDef("QRY_custdata")

SQLstring = "SELECT * FROM tbl_customers_data WHERE CUST_NAME = " &
what_comp & ""
qry.Sql = SQLstring

Set rs = qry.OpenRecordset()

Sheets("Sales").Activate

[a2].CopyFromRecordset rs
db.QueryDefs.Delete "QRY_custdata"
db.Close

End sub

 

[Jean-Yves]

Hi,

Try the following,

what_comp = frm_invoice.cmb_custname.Text
....
SQLstring = "SELECT * FROM tbl_customers_data WHERE CUST_NAME = ' " &
what_comp & " ' "

(I have added space between double and single quote for reading)
In certain occasions, you may as well have problems with the length of the
SQL string.
I use the following from Microsoft :
.....
'convert the string into array
varSql = StringToArray(SQLstring)
qry.Sql = varSql
....
Function StringToArray(Query As String) As Variant

Const StrLen = 127 ' Set the maximum string length for
' each element in the array to return
' to 127 characters.
Dim NumElems As Integer
Dim Temp() As String
Dim i
' Divide the length of the string Query by StrLen and
' add 1 to determine how many elements the String array
' Temp should contain, and redimension the Temp array to
' contain this number of elements.

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

' Build the Temp array by sequentially extracting 127
' segments of the Query string into each element of the
' Temp array.

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

' Set the function StringToArray to the Temp array so it
' can be returned to the calling procedure.

StringToArray = Temp

End Function

Regards

Jean-Yves

 

[peregenem]

Good afternoon,

The below query works when the string value has no apostriphies e.g
jack wines, however it crashes over when it's Jack's wines.

Set qry = db.CreateQueryDef("QRY_custdata")

SQLstring = "SELECT * FROM tbl_customers_data WHERE CUST_NAME = " &
what_comp & ""

If I was doing this in ADO, I'd make the sql text be

SELECT * FROM tbl_customers_data WHERE CUST_NAME = ?

and use Command and Parameter objects to pass the String value. The Jet
provider would take care of escaping characters and the like.

Actually, I wouldn't use SELECT * in a production system. And I
wouldn't prefix my table names with tbl_ (it breaks ISO naming rules
but more importantly it would make me look like an Access developer
I'd also have earlier interrogated the schema and have columns info for
parameters pre loaded, perhaps using the OpenSchema method, so I could
get the correct data type, width, nullability, etc. Actually, I'd
create stored PROCEDURES in the database and call the procedure.....