SQL Statement gets different results between Query Analyzer and VBA

[RayMilhon]

I have a command button on an Access 2010 Form that populates a recordset and sets the form filter based on the results. The recordset is opened with the following statement

rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

The recordset returns 0 Records.

If I put in a breakpoint at the rs.open statement and then go into the immediate window and put in ?strsql I get the SQL Statement that is executed. I then copy that statement into Query analyzer and run it. I get the 1 record I expect to get there.



Why would I get different results between running a query in the Analyzer and running the exact same query in Code?

[orange]

What's the value in strsql?

[RayMilhon]

Select memb_keyid from dbo_current_membership where lastnm like "Milhon*"

[June7]

Different engines, different wildcards.

Building sql with VBA and opening recordset with wildcard parameter doesn't work with * wildcard. Try %. Been there, been very frustrated. Had to modify code because couldn't get * to work. Several years later finally figured out the issue.

EDIT: As Paul explains below, the conflict is due to DAO vs ADO recordset.

[RayMilhon]

Thanks that was it.

[pbaldy]

To clarify, use * with a DAO recordset and % with an ADO recordset. I suppose the left hand not knowing what the right hand is doing at MS. Don't shoot me, I'm just the messenger.