Recordset Open method is strange

[bavjean]

Hi all,

I am running a piece of code that should be exporting a recordset to Excel,
but I am having some difficulties with the .Open method of a Recordset
object..

I have the following as my strSQL which I will pass to the recordset to open:

SELECT A.VPNummer, A.Nachname, A.Vorname, A.Abteilung, A.Telefon, A.Sex AS
Geschlecht, A.VFG, A.Körperhöhe, A.StammlProp FROM qryAlleVPmEndwerte A WHERE
(A.Sex) = 'm' AND (A.Körperhöhe) >= 1750 AND (A.Körperhöhe) <= 1850 AND
((A.StammlProp) Like '*MM*') AND (A.VFG) = True ORDER BY A.VPNummer;

Then, when I use this in the following code, I get no records in the
recordset!

dim rstData as ADODB.Recordset
rstData.ActiveConnection = CurrentProject.Connection
rstData.Open strSQL

I checked the VB editor, in the Locals window for the Status property of
rstData, and it says „<Either BOF or EOF is true…>“ . The SQL does however
return numerous records when I copy and paste it directly into the query
design window’s SQL view.

My suspicion lies in the following few lines:

WHERE ……….((A.StammlProp) Like '*MM*') ………………….

Whenever I have something similiar to this(i.e. a string in the WHERE
clause), the abovementioned problems happen.

I have been looking at this for hours now, and checking numerous help sites
out, but I am stilll stuck. Would be very grateful if someone could help.

Kind Regards,
Jean

 

[bavjean]

I think I found the problem.

When using ADODB, the wildcard character is "%" not "*"

Thanks for the other tips though