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

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




"marcbell" wrote in message
oups.com...
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

marcbell wrote:
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.....