crazy recordset behaviour

[tranchemontaigne]

I have a procedure that uses a recordset. The recordset is set to a string that contains an SQL statement. I can run the SQL statement in query view and return about 300 records. When I try to open the recordset defined by this SQL statement I receive no records. What's happening? Here's a code excerpt.

Set rstDeathCertExtract = New ADODB.Recordset
strActiveConnection = CurrentProject.Connection
rstDeathCertExtract.Open strSQL_Source, strActiveConnection, adOpenForwardOnly

intCounter = 0
Do Until rstDeathCertExtract.EOF
intCounter = intCounter + 1
rstDeathCertExtract.MoveNext
Loop
Debug.Print intCounter & " death certificates were selected in first pass of search" & Chr(10)

strSQL_Source = "SELECT ST_FILE_NBR, DECD_DTH_DT, DECD_AGE_YR, DECD_SEX, DECD_DTH_LOC_GEO_CD, DECD_RES_GEO_CD, Investigation, Outcome, Reportable, PrimaryCondition, AllDeathConditions FROM tblRecordsWithDeathTerms WHERE (((DECD_DTH_DT)>=#1/1/2002# And (DECD_DTH_DT)<=#1/24/2002#) AND ((DECD_AGE_YR)>=0 And (DECD_AGE_YR)<=120) AND (( Investigation) Like "*") AND (( Status) Like "*") AND (( Outcome) Like "*") AND (( Reportable) Like "*") AND (( Agent) Like "*"));
________
Z-SERIES

 

[Scooterbug]

strSQL_Source = "SELECT ST_FILE_NBR, DECD_DTH_DT, DECD_AGE_YR, DECD_SEX, DECD_DTH_LOC_GEO_CD, DECD_RES_GEO_CD, Investigation, Outcome, Reportable, PrimaryCondition, AllDeathConditions FROM tblRecordsWithDeathTerms WHERE (((DECD_DTH_DT)>=#1/1/2002# And (DECD_DTH_DT)<=#1/24/2002#) AND ((DECD_AGE_YR)>=0 And (DECD_AGE_YR)<=120) AND (( Investigation) Like "*") AND (( Status) Like "*") AND (( Outcome) Like "*") AND (( Reportable) Like "*") AND (( Agent) Like "*"));

is that exactly how you have it defined in VBA? If so, it's the quotes. When you need to have quotes inside of quotes, you have to do it differently. Normally I use chr(34) cause I can't for the life of me remember the rule for using quotes within quotes. A search of the forums will, however turn up the correct usage.

as an example, you would need to do this:

strSql = "SELECT ST_FILE_NBR, DECD_DTH_DT, DECD_AGE_YR, DECD_SEX, DECD_DTH_LOC_GEO_CD, DECD_RES_GEO_CD, Investigation, Outcome, Reportable, PrimaryCondition, AllDeathConditions "
strSql = strSql & "FROM tblRecordsWithDeathTerms "
strSql = strSql & "WHERE (((DECD_DTH_DT)>=#1/1/2002# And (DECD_DTH_DT)<=#1/24/2002#) "
strSql = strSql & "AND ((DECD_AGE_YR)>=0 And (DECD_AGE_YR)<=120) "
strSql = strSql & "AND (( Investigation) Like " & [COLOR=red]chr(34) & "[/COLOR]*" & chr(34) & ")"
etc, etc, etc....

 

[WayneRyan]

tranchemontaigne,

Change your wildcard for the ADO recordset to --> %

hth,
Wayne

 

[tranchemontaigne]

WayneRyan,

Changing the wildcard in VBA to a percentage did the trick - I'm very surprosed to see that VBA wildcards and standzrd MS Access query wildcards are not the same.

Thanks!
________
Mflb Vaporizer