Counting month fields for the current year?

[Caspius]

Hiya

This is my first post here, but I've two questions so I'll divide it into two threads.

Normally I can google these to help find the answers but a bit of a loss at this one, probably beginners stuff to some of you.

Heres a link to the database below

http://casp.gamecommunity.co.uk/database.gif

Total count is easy: =Count(tblMain!ID1)

What I want to do is count the amount of records for each individual month of the current year so I don't have to change the year date everytime a new year comes round. As soon as the next year comes around they all reset to 0.

The clever way would be to count the records for the current month -1 current month -2 etc and automatically update the month labels but I think that would be getting a bit comlicated.

Thanks in advance for any help.

 

[WayneRyan]

Caspius,

Select Month([YourDateField]), Count(*)
From   YourTable
Group By Month([YourDateField])
Having Year([YourDateField]) = Year(Date())

Wayne

 

[Caspius]

Thanks for your reply but I didn't explain myself very well.

On the on the frmSwitchboard in the picture, link, I have a column of text boxes with the month next to each one. I want to show in each text box the amount of fields that were entered during current year in that month. The Date field is called [Dt] and the table where the data comes from is called [tblMain].

I did work this out for January date but it keeps coming up with error, not sure what I am doing wrong!

=DCount("[dt]","tblMain","between dateserial(Date(),1,1) and dateserial(Date(),1,31")

Thanks

 

[Brianwarnock]

Try
=DCount("[dt]","tblMain","[dt] between dateserial(year(Date()),1,1) and dateserial(year(Date()),1,31")

Brian

 

[Caspius]

Try
"between dateserial(year(Date()),1,1) and dateserial(year(Date()),1,31)"

Brian

Thanks.

Just noticed I missed the year off.

Hi thanks, just noticed myself I missed the Year part of the dateserial off, but you bet me.

=DCount("[dt]","[tblMain]","[dt] between dateserial(Year(Date()),51,1) and dateserial(Year(Date()),51,31)")

Will that include the 31st and the 1st in the count?

 

[Brianwarnock]

That's a point it wont include the 31st if you have any times in your dt as the date serial will default to 00:00:00, but if you dont have times then you will be ok.

Brian

ps what's the 51 in the month parameter.?

 

[Caspius]

That's a point it wont include the 31st if you have any times in your dt as the date serial will default to 00:00:00, but if you dont have times then you will be ok.

Brian

ps what's the 51 in the month parameter.?

My mistake, 51 is a typo... The time field is seperate, [Dt] only has day, month and year. I'll give it a test run anyway.

Currently half my problem with Access is I used to be able to do all this but I have'nt even touched the program for 3 years, and most of my knowledge seems to have leaked away. Very frustrating.

I just need to sort this one out now. http://www.access-programmers.co.uk/forums/showthread.php?t=138142

Thanks though.