Results 1 to 6 of 6
  1. #1
    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. #2
    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 ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    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

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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")
    should fix your dcount problem
    however if you are proposing to do 12 dcoutns per year, and n years then a table redesign is a good idea
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    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
  •