Apostrophies SQL from Access
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
|