SQL query returns 0 records in vba 1

[rainbowrabbit]

Hi,

I can't see what I am doing wrong here

I am concatenating an SQL string to replace apostrophe's from surnames so I can stick them in an xml file. I concatentate the query,

strSQL = ""
strSQL = strSQL & "SELECT tblEMSdata.CHILDIDENTIFIERVALUE, tblEMSdata.FAMILYNAME, tblEMSdata.GIVENNAME1, tblEMSdata.GIVENNAME2, tblEMSdata.LINE3 "
strSQL = strSQL & "FROM tblEMSdata WHERE (("
strSQL = strSQL & "(tblEMSdata.FAMILYNAME) Like ""*" & Chr(39) & "*""))"

rst.Open strSQL, CurrentProject.Connection, adOpenKeyset

which then produces the following query,

SELECT tblEMSdata.CHILDIDENTIFIERVALUE, tblEMSdata.FAMILYNAME, tblEMSdata.GIVENNAME1, tblEMSdata.GIVENNAME2, tblEMSdata.LINE3 FROM tblEMSdata WHERE (((tblEMSdata.FAMILYNAME) Like "*'*"))

When I paste this into a new SQL query window within access it returns 141 records but in the vba, rst.recordcount = 0??

help!

 

[Remou]

The wildcard for ADO is %, not *. Are you using ADO?

 

[rainbowrabbit]

thank you Remou it was the wildcards and yes I am using ADO!!