How do i count date fields?

[bogushs]

Hi there. Im trying to work out how I can count the dates in my database per month. What ive done is created an unbound box with an sql statement

SELECT Count([tbl Main].[Date of Call]) AS [CountOfDate of Call]
FROM [tbl Main]
HAVING (((Count([tbl Main].[Date of Call])) Between #4/1/2006# And #4/6/2006#));

When I select the totals button to count the records 0 are pulled out.

Does anyone know if im doing something wrong here. As always im sure its quite simple..

Cheers

 

[ColinEssex]

SELECT tablemain.Date of call, Count(BaseData.Date of call) AS CountOfDate of call
FROM tablemain
GROUP BY tablemain.date of call
HAVING (((tablemain.Date of call) Between #4/1/2006# And #4/6/2006#));


Col

 

[bogushs]

hi there. thanks for your help. ive tried adjusting the sql statement but it doesnt seem to work. i was wondering whether it is possible to do something like this in expression builder?

thanks

 

[Brianwarnock]

Do you need all those () in the Having statement should it be
HAVING ([tablemain.Date of call] Between #4/1/2006# And #4/6/2006#);

 

[Brianwarnock]

Ooops!! as it is an aggregate query you need a Where clause not a Having

WHERE (tablemain.[Date of call] Between #4/1/2006# And #4/6/2006#);

Brian

 

[bogushs]

hi there


im having trouble with the below syntax when trying to dun the sql

Count(BaseData.Date of call)

Im not sure how this fits in??

 

[Brianwarnock]

The spaces in your field names are causing you problems, try surrounding them in []

Brian

 

[namliam]

Hence as a rule of thumb, dont use spaces in names of tables, columns, queries, forms, etc.

it only leads to trouble