Counting Fields in a Report with Conditions

[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

 

[KARL DEWEY]

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

 

[TomP]

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

 

[KARL DEWEY]

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

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

 

[KARL DEWEY]

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

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

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

 

[TomP]

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

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

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

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