Vba sql + dcount

[habiler]

Hi Everybody,

Whats wrong with my code ??
newSQL1 = "SELECT CODE, DCOUNT(PERIODE,Maladies,"PERIODE = '01/01/2017'")AS Teller FROM Maladies "
Habiler

 

[Minty]

Dates in access must be surrounded by # # in access. Also your query isn't efficient, you shouldn't use domain functions (DCount, Dlookup etc ) in a query.

You can rewrite it as

"SELECT Code , Count(Periode) as Teller FROM Maladies WHERE Periode = #01/01/2017# Group By Code"

 

[plog]

I vote that the thing wrong with your code is the DCount appearing at all. There's no reason for Domain functions (DCount, DMax, etc.) in SQL. Instead you build an aggregate query to get that information:

SELECT COUNT(PERIODE) AS Teller FROM Maladies WHERE PERIODE=#1/1/2017;