Counting Fields in a Report with Conditions

  • Thread starter Thread starter TomP
  • Start date Start date
T

TomP

How can I rewrite this code to where I can get a total number count of
caseloads that meets three possible conditions? The conditions for the
CLOSURE, FINAL, and ACKNHRG are all date fields. Basically, if any of those
fields have a date, then I would like to get a "head count" of the caseloads.


I'm aware that the way it is written now is looking for every condition and
I get a zero count. I tried to replace it with an OR, but that didn't work.

See Below....

=Abs(Sum(IIf([CASELOAD]="NS",1) And ([CLOSURE] Is Not Null) And ([FINAL] Is
Not Null) And ([ACKNHRG] Is Not Null)))

Thank you again for your time and help!

Tom
 
Try this ---
=Sum(IIf([CASELOAD]="NS" And ([CLOSURE] Is Not Null) And ([FINAL] Is Not
Null) And ([ACKNHRG] Is Not Null), 1, 0))
 
I tried it and it still doesn't work... The total gives me a zero.... :-(

KARL DEWEY said:
Try this ---
=Sum(IIf([CASELOAD]="NS" And ([CLOSURE] Is Not Null) And ([FINAL] Is Not
Null) And ([ACKNHRG] Is Not Null), 1, 0))

--
KARL DEWEY
Build a little - Test a little


TomP said:
How can I rewrite this code to where I can get a total number count of
caseloads that meets three possible conditions? The conditions for the
CLOSURE, FINAL, and ACKNHRG are all date fields. Basically, if any of those
fields have a date, then I would like to get a "head count" of the caseloads.


I'm aware that the way it is written now is looking for every condition and
I get a zero count. I tried to replace it with an OR, but that didn't work.

See Below....

=Abs(Sum(IIf([CASELOAD]="NS",1) And ([CLOSURE] Is Not Null) And ([FINAL] Is
Not Null) And ([ACKNHRG] Is Not Null)))

Thank you again for your time and help!

Tom
Click to expand...
Click to expand...
 
Re-read your requirement so try it this way --
Sum(IIf([CASELOAD]="NS" And ([CLOSURE] Is Not Null) OR ([FINAL] Is Not Null)
OR ([ACKNHRG] Is Not Null), 1, 0))

--
KARL DEWEY
Build a little - Test a little


TomP said:
I tried it and it still doesn't work... The total gives me a zero.... :-(

KARL DEWEY said:
Try this ---
=Sum(IIf([CASELOAD]="NS" And ([CLOSURE] Is Not Null) And ([FINAL] Is Not
Null) And ([ACKNHRG] Is Not Null), 1, 0))

--
KARL DEWEY
Build a little - Test a little


TomP said:
How can I rewrite this code to where I can get a total number count of
caseloads that meets three possible conditions? The conditions for the
CLOSURE, FINAL, and ACKNHRG are all date fields. Basically, if any of those
fields have a date, then I would like to get a "head count" of the caseloads.


I'm aware that the way it is written now is looking for every condition and
I get a zero count. I tried to replace it with an OR, but that didn't work.

See Below....

=Abs(Sum(IIf([CASELOAD]="NS",1) And ([CLOSURE] Is Not Null) And ([FINAL] Is
Not Null) And ([ACKNHRG] Is Not Null)))

Thank you again for your time and help!

Tom
Click to expand...
Click to expand...
Click to expand...
 
One more try --
=Sum(IIf([CASELOAD]="NS" And (([CLOSURE] Is Not Null) OR ([FINAL] Is Not
Null)
OR ([ACKNHRG] Is Not Null), 1, 0)))

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Re-read your requirement so try it this way --
Sum(IIf([CASELOAD]="NS" And ([CLOSURE] Is Not Null) OR ([FINAL] Is Not Null)
OR ([ACKNHRG] Is Not Null), 1, 0))

--
KARL DEWEY
Build a little - Test a little


TomP said:
I tried it and it still doesn't work... The total gives me a zero.... :-(

KARL DEWEY said:
Try this ---
=Sum(IIf([CASELOAD]="NS" And ([CLOSURE] Is Not Null) And ([FINAL] Is Not
Null) And ([ACKNHRG] Is Not Null), 1, 0))

--
KARL DEWEY
Build a little - Test a little


:

How can I rewrite this code to where I can get a total number count of
caseloads that meets three possible conditions? The conditions for the
CLOSURE, FINAL, and ACKNHRG are all date fields. Basically, if any of those
fields have a date, then I would like to get a "head count" of the caseloads.


I'm aware that the way it is written now is looking for every condition and
I get a zero count. I tried to replace it with an OR, but that didn't work.

See Below....

=Abs(Sum(IIf([CASELOAD]="NS",1) And ([CLOSURE] Is Not Null) And ([FINAL] Is
Not Null) And ([ACKNHRG] Is Not Null)))

Thank you again for your time and help!

Tom
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much for your input.... You led me in the right direction! :-)

Here is what I used ....

=Count(IIf([CASELOAD]="NS",1 And (([CLOSURE]=True) Or ([FINAL]=True) Or
([ACKNHRG]=True))))

I'm not sure why the "True/False" worked instead using the "Null/Is Not
Null" and also using the "Count" instead of "Sum".

Have a nice day and a BIG THANK YOU!

Tom


KARL DEWEY said:
One more try --
=Sum(IIf([CASELOAD]="NS" And (([CLOSURE] Is Not Null) OR ([FINAL] Is Not
Null)
OR ([ACKNHRG] Is Not Null), 1, 0)))

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Re-read your requirement so try it this way --
Sum(IIf([CASELOAD]="NS" And ([CLOSURE] Is Not Null) OR ([FINAL] Is Not Null)
OR ([ACKNHRG] Is Not Null), 1, 0))

--
KARL DEWEY
Build a little - Test a little


TomP said:
I tried it and it still doesn't work... The total gives me a zero.... :-(

:

Try this ---
=Sum(IIf([CASELOAD]="NS" And ([CLOSURE] Is Not Null) And ([FINAL] Is Not
Null) And ([ACKNHRG] Is Not Null), 1, 0))

--
KARL DEWEY
Build a little - Test a little


:

How can I rewrite this code to where I can get a total number count of
caseloads that meets three possible conditions? The conditions for the
CLOSURE, FINAL, and ACKNHRG are all date fields. Basically, if any of those
fields have a date, then I would like to get a "head count" of the caseloads.


I'm aware that the way it is written now is looking for every condition and
I get a zero count. I tried to replace it with an OR, but that didn't work.

See Below....

=Abs(Sum(IIf([CASELOAD]="NS",1) And ([CLOSURE] Is Not Null) And ([FINAL] Is
Not Null) And ([ACKNHRG] Is Not Null)))

Thank you again for your time and help!

Tom
Click to expand...
Click to expand...
Click to expand...
 
Back
Top