Query doesn't return the same result in code

[remig via AccessMonster.com]

I have a query which scans for missing data in a table. When I run in query
designer, I get 0 rows back (expected result).

Now if I go in the vba code and do this :
CurrentProject.Connection.Execute("SELECT COUNT(*) AS Total FROM
vwSimdutsSansPictogrammes").Fields("Total").Value

The count(*) returns 4 rows when it should return 0... Is there some sort of
cache that I need to empty??

There are no parameters, I closed access and even did a compact / repair.
I'm sure I'm using the right query and that I'm in the right DB.

Any help is greatly appreciated.


Running on Access 2007, all updates as far as I know. Running a db in 2000
mode.

Here's the query in case you need it :

SELECT Simduts.NomSimdut, Simduts.PathSimdut
FROM Simduts LEFT JOIN SimdutsPictogrammes ON Simduts.PkSimdut =
SimdutsPictogrammes.FkSimdut
WHERE (((Simduts.NomSimdut) Not Like 'SS003*') AND ((SimdutsPictogrammes.
FkPictogramme) Is Null))
ORDER BY Simduts.NomSimdut;

 

[remig via AccessMonster.com]

That's even weirder, I just run the select count(*) straight in query
designer... it still shows 4. But if I do select * From... I get no data
back!!!

Does that mean that the count is evaluated BEFORE the where????

That's the only explaination I can think of for this behavior.

 

[Allen Browne]

JET can't count the wildcard.

Alternatives:
=DCount("*", "vwSimdutsSansPictogrammes")

=Currentdb.OpenRecordset("SELECT Count(NomSimdut) AS HowMany
FROM Simduts LEFT JOIN SimdutsPictogrammes
ON Simduts.PkSimdut = SimdutsPictogrammes.FkSimdut
WHERE ((Simduts.NomSimdut Not Like 'SS003*')
AND (SimdutsPictogrammes.FkPictogramme Is Null));").Fields(0)

 

[remig via AccessMonster.com]

Thanks for the awesome answer. I had forgotten about the dlookups functions..
. 6 straights years on sql server will do that .

Thanks again.

JET can't count the wildcard.

Alternatives:
=DCount("*", "vwSimdutsSansPictogrammes")

=Currentdb.OpenRecordset("SELECT Count(NomSimdut) AS HowMany
FROM Simduts LEFT JOIN SimdutsPictogrammes
ON Simduts.PkSimdut = SimdutsPictogrammes.FkSimdut
WHERE ((Simduts.NomSimdut Not Like 'SS003*')
AND (SimdutsPictogrammes.FkPictogramme Is Null));").Fields(0)

I have a query which scans for missing data in a table. When I run in
query

[quoted text clipped - 25 lines]

FkPictogramme) Is Null))
ORDER BY Simduts.NomSimdut;