# Thread: Help required for date calculation in report

1. Registered User
Join Date
Sep 2005
Posts
240

## Unanswered: Help required for date calculation in report

Hi folks,

I need some help trying to get a calculation working.

In my report I want to count the number of events per month, sounds simple, but I can't figure this out at all.

I have tried the following which works correctly without criteria, but when I add the criteria in to count the events in a particular month it doesn't work.

Code:
`=DCount("[EventType]","qryRptEventSummary","[EventDate]"=Month(10))`
So what am I doing wrong?

Thanks

2. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
You need to compare apples to apples ... In your case, you need to compare the MONTH of the event date to the desired month ...

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
try something like.......
=DCount("[EventType]","qryRptEventSummary","month([EventDate])=10")

domain fucntions are a closed book to me, I hate 'em due to potentail performance problems

4. Registered User
Join Date
Sep 2005
Posts
240
Thanks Healdem that worked great, but then a hit a problem because I also need to filter on the year as well. I did try the following but it brought back all the records regardless of the month:

Code:
`=DCount("[EventType]","qryRptEventSummary","Month([EventDate])=01" And "Year([EventDate])=2006")`
Basically I need a report to summarise and count up numbers by month and year, because I work at a college it has to be academic year (eg sept 05 - aug 06).

So what would be the best way of doing this?

Thanks

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
in an ideal world some form of table storing your academic calendar so that you cna realte real world dates into academic world calendars

Code:
`=DCount("[EventType]","qryRptEventSummary","Month([EventDate])=01 And Year([EventDate])=2006")`
however if you are proposing to do 12 dcoutns per year, and n years then a table redesign is a good idea

6. Registered User
Join Date
Sep 2005
Posts
240
OK, I've been thinking about what you said with regards to produce so many documents a year. Since it is only the year that will change in the calculation and not the months then I will ask them what academic year they want with a pop-up form.

So they enter the year into a textbox (e.g 2005/2006), in my report I then get the first year, then the last year and use that in my calculation, but it's not working it just comes back with no results

OK this is my code in the report:

Code:
```Private Sub Report_Open(Cancel As Integer)
Dim strAccYear As String
Dim strPrevYear As Integer
Dim strNextYear As Integer

strAccYear = Form_frmAccYear!txtAccYear
strPrevYear = Left(strAccYear, 4)
strNextYear = Right(strAccYear, 4)

End Sub```
And this is the calculation:

Code:
`=DCount("[EventType]","qryRptEventSummary","Month([EventDate])=9 AND Year([EventDate])='strPrevYear'")`
The code in the report seems to work, but the calculation doesn't.

Also my report brings back 126 records which is what the query brings back, is there any way of getting it to just show one.

Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•