Recordset Open method is strange

[antifashionpimp]

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

 

[antifashionpimp]

I think I found the problem.

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

 

[cable]

also isn't like case sensitive? ie *MM* wont match ommo but *mm* will match ommo and OMMO

at least I thought that was the case.

 

[antifashionpimp]

Doesn't matter in my case as the *MM* part is hard-coded and will never be lower case.

Not sure actually, but I know that I should watch out for these subtle differences now.