Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: Problem with counting subsets of records

    Good morning, I am using Access 2013. In a report I have a field that has three possible values: Fire, EMS or Other. The fields in the table I am using are lookup fields, so the files are long integer. The lookup table fields are text fields.

    In a report I want to count the number of records for each value (3 text boxes). I have tried i.e.:

    =Sum(IIf([Call Type]="Fire",1,0)) (I seem to remember that this should be the correct way to do this)
    =Count(IIf([Call Type]="Fire",0))

    I get the error: "Data type mismatch in criteria expression". Can anyone give me a clue as to what I am doing wrong?

    Scratching my head
    Thanks, Scott

  2. #2
    Join Date
    Sep 2014
    Posts
    37
    =Count(IIf([Call Type]="Fire",1,0))
    not

    =Count(IIf([Call Type]="Fire",0))

  3. #3
    Join Date
    Mar 2013
    Posts
    70

    Problem with counting subsets of records

    Quote Originally Posted by slynde View Post
    =Count(IIf([Call Type]="Fire",1,0))
    Hi, thanks so much for the response. This still give me a data type mismatch error.

    Best, Scott

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How about
    Code:
    SELECT Count([Call Type]) FROM TableName WHERE [Call Type]='Fire')
    Have a nice day!

  5. #5
    Join Date
    Sep 2014
    Posts
    37
    doh... yup that's better Sinndho

  6. #6
    Join Date
    Mar 2013
    Posts
    70

    Problem with counting subsets of records

    Quote Originally Posted by slynde View Post
    doh... yup that's better Sinndho
    That works fine. Thanks. Got that part working well.

    Now I just discovered a new problem I need to resolve. I have a data parameter in the query running the report. That works fine. But I just noticed that my year to date calculations in the report footer do not have the right data with the date parameter. When opening the report with all dates I get the proper ytd data. When I use the parameter, I get the same data I get for the date from the parameter. Is there a way to get ytd while still filtering the date down to a month or two at a time? Many thanks.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    rather than use a 'select' for this I'd either build the count into the base query (assuming it can be done) or run my own internal counters as part of the report

    in this case I'd probably use some VBA inside the report
    declare as many variables as required at the 'top' of the reprot VBA code module. ie as public variable inside that report

    for each row in the detail on format event add to the relevant variables
    then for each grouping place code which 1 assigns the values to controls in the relevant format event, then reset the group variable


    ...you cant get the same effect (with a lot less work ) by getign the reports underlying query to set a value for each of the call types


    eg
    Code:
    select
      my,
      column,
      list,
      iif(nz([call type],"") = 'Fire',1,0) as FireEvent
      iif(nz([call type],"") = 'EMS',1,0) as EMSEvent
      iif(nz([call type],"") = 'Other',1,0) as OtheEvent
    from mytablee
    then add a hidden control for each of those columns in your query
    then add a 3 cotnrols in each of your group footers as required which sums the value of the control in the report detail.

    eg =sum(EMSEvent)

    the NZ should handle the possibility of a null value causing a data mismatch error.

    the reason why your subtotals dont' match you report is that (Im guessing) you are issuing a series of select statements or dcounts (or other domaion fuicntions) without the matching date limit in the where clause of thise elements.


    either you need to add that date limiting OR use the technique described above to only refer to the data within the report itself. UNLESS you explicitly set a date limit as part of your select or domain function then the program will get EVERY record that matches your criteria.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2013
    Posts
    70
    hello healdem, thanks for the reply. I am afraid I do not understand your reply. Maybe I was unclear. A report with a parameter query using a date range. In report footer I have a series of totals for year to date. When the date range is set to the years date range I get correct YTD data. If I choose a month the footer data is for the month too. This I understand. Is there a way around this so I get ytd while still getting month data from parameter query?

Posting Permissions

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