Counting Based on Multiple Conditions

[STEVENCV]

Hi,

I am creating a report and need to count the number of clients that are within the first 48 hours of the service, and also count clients that are above 48 hours. I also need to count clients whose service has ended, but count those that ended within 48 hours and those that ended after 48 hours.

The fields I have are:

service_start_date_time This is a long date and time field.
service_end_date_time This is a long date and time field.
package_accepted This is a tickbox to say whether a package was accepted or not.
minutes This is a caculated field in the query I am using for the report. Using Minutes: DateDiff("n",[service_start_date_time],IIf([service_end_date_time] Is Null,Date(),[service_end_date_time]))
hours this is a calculated field in the query I am using for the report. Using Hours: [Minutes]\60 & Format([Minutes] Mod 60,"\:00")

EDIT: I have added a field to the report called onservice which uses this: =IIf([service_end_date_time] Is Null And [package_accepted]=True,"Yes","No")
Thought it might help.

The four counts I need to do are:

1) Clients Still On Service Within First 48 Hours. Criteria for this is package_accepted is ticked, hours is less than 48 and service_end_date_time is blank EDIT: Instead of package_accepted true and service_end_date_time blank, you could use the new onservice field which states Yes or No if they are on service)
I've been trying this: =Sum(IIf([service_end_date_time] Is Null And [package_accepted]=True And [Hours]<48,1,0)) but it doesn't like that at all.


2) Clients Still On Service Beyond First 48 Hours. Criteria for this is package_accepted is ticked, hours is more than 48 and service_end_date_time is blank. EDIT: Instead of package_accepted true and service_end_date_time blank, you could use the new onservice field which states Yes or No if they are on service)


3) Clients Whose Service Ended Within First 48 Hours. Criteria for this is package_accepted is ticked, hours is less than 48 and service_end_date_time has a value in it. EDIT: Instead of package_accepted true and service_end_date_time , you could use the new onservice field which states Yes or No if they are on service)


3) Clients Whose Service Ended Beyond First 48 Hours. Criteria for this is package_accepted is ticked, hours is more than 48 and service_end_date_time has a value in it. EDIT: Instead of package_accepted true and service_end_date_time blank you could use the new onservice field which states Yes or No if they are on service)

I've tried loads of different SUM/IIF/AND/COUNT expressions, but I cannot seem to get it to work at all.

Any help you could give me would be amazing, because I'm up against a tight deadline and I've hit a brick wall.

 

[sneuberg]

Edit: I see now that this is in the report and not the query so ignore this for now. But if [package_accepted] is not in the query but only a textbox in the report I don't think it will work that way.

I've been trying this: =Sum(IIf([service_end_date_time] Is Null And [package_accepted]=True And [Hours]<48,1,0)) but it doesn't like that at all.

What do you mean by it doesn't like that at all? What is happen?

I've found then when I use a calculated field in another calculated field (Like [Hours] ) I need to have it in the SELECT clause (It needs to be checked) and the query needs to be saved (best to close and reopen) after the first calculated field is working. Probably wouldn't hurt to qualify it e.g,. qryName!Hours.

If you can't get that work then I suggest expanding [Hours] so that this calculated field doesn't have other calculated fields. Or put all this in a public function in a standard module and use the function in the query.

 

[STEVENCV]

Thanks.

I have a field on the report that counts all the users still on the service by using: =Sum(IIf([service_end_date_time] Is Null And [package_accepted]=True,1,0))

So the package_accepted works there. It's the hours field I think that is causing problems. I keep getting "data mismatch" errors.

I'm not sure how to add the field to a select clause but my SQL page for the report says:

SELECT tbl_clients.ID, tbl_clients.title, tbl_clients.forename, tbl_clients.surname, tbl_clients.address_line_one, tbl_clients.address_line_two, tbl_clients.address_town, tbl_clients.address_county, tbl_clients.address_postcode, tbl_clients.reference_number, tbl_clients.referral_date, tbl_clients.referral_source, tbl_clients.package_accepted, tbl_clients.reason_for_non_acceptance, tbl_clients.service_start_date_time, tbl_clients.service_end_date_time, tbl_clients.reason_for_service_end, DateDiff("n",[service_start_date_time],IIf([service_end_date_time] Is Null,Date(),[service_end_date_time])) AS Minutes, [Minutes]\60 & Format([Minutes] Mod 60,"\:00") AS Hours
FROM tbl_clients;

 

[sneuberg]

Hours: [Minutes]\60 & Format([Minutes] Mod 60,"\:00")

This returns a string which might be causing your error. I suggest creating another calculate field let's say NumberOfHours that returns a numeric result and use that in in the Sum calculation, e.g.

=Sum(IIf([service_end_date_time] Is Null And [package_accepted]=True And [NumberOfHours]<48,1,0))

 

[STEVENCV]

Thank you for your help. I understand the logic of that. However, how would I calculate the "NumberofHours" field so that it's a numeric result?

 

[sneuberg]

How about:

NumberOfHours:  [Minutes] \ 60 + ([Minutes] Mod 60) / 60

That would product a decimal number of hours, e.g., 150 minutes would be 2.5 hours.

 

[sneuberg]

Or more directly

NumberOfHours: DateDiff("[COLOR="DarkRed"]h[/COLOR]",[service_start_date_time],IIf([service_end_date_time] Is Null,Date(),[service_end_date_time]))

 

[STEVENCV]

You sir, are a genius.

Using your NumberofHours worked perfectly!

Thank you very much for your help, and your swift responses. It is much appreciated.