"0" rather than empty field

[Mike Hughes]

When I run the query below I get an empty field in the “% ARR PAID” field when there are no arrears paid.

I’m assuming the error is in this line:
IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]=0,Null,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]/[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED]) AS [% ARR PAID]

I would like to have show ‘0’rather than an empty field.

Can someone explain to me how to do this?

SELECT
NOLDBA_REF_CASE_WORKER.WORKER_NAME AS NAME,
NOLDBA_REF_CASE_WORKER.WORKER_ID AS CASELOAD,
Sum(NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.TOTAL_DISTRIBUTED) AS [TOTAL DIST],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES AS [OPEN],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES AS CLOSED,
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE AS [EST O],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED AS [CURR OWED],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID AS [CURR PAID],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED AS [ARR OWED],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID AS [ARR PAID],
IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]=0,Null,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]/[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED]) AS [% ARR PAID]
FROM NOLDBA_DISTRIB_BY_DIST_RESULT_TBL INNER JOIN NOLDBA_REF_CASE_WORKER ON NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.WORKER_ID = NOLDBA_REF_CASE_WORKER.WORKER_ID

GROUP BY NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE

HAVING (((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM)=[YYYYMM]) AND ((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE)=[DISTRICT]))

ORDER BY NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID;

Thanks Mike

 

[JANR]

[B]IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]=0,[COLOR=red]0[/COLOR],[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]/[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED]) AS [% ARR PAID][/B]

Null is a "blank" field
0 is 0

JR

 

[Galaxiom]

If Arrears Paid is zero then the calculation will also be zero so the IIF is not necessary to get a zero in the field.

However if Arrears Owed is zero you will get an error.

 

[Mike Hughes]

That worked great, thanks!
Now just one more question.
What if ARREARS OWED=0 and ARREARS PAID is greater than 0
How and where would I put that into the query?


SELECT
NOLDBA_REF_CASE_WORKER.WORKER_NAME AS NAME,
NOLDBA_REF_CASE_WORKER.WORKER_ID AS CASELOAD,
Sum(NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.TOTAL_DISTRI BUTED) AS [TOTAL DIST],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES AS [OPEN],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES AS CLOSED,
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE AS [EST O],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED AS [CURR OWED],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID AS [CURR PAID],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED AS [ARR OWED],
NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID AS [ARR PAID],
IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]=0,0,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]/[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED]) AS [% ARR PAID]
FROM NOLDBA_DISTRIB_BY_DIST_RESULT_TBL INNER JOIN NOLDBA_REF_CASE_WORKER ON NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.WORKER_ID = NOLDBA_REF_CASE_WORKER.WORKER_ID

GROUP BY NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE

HAVING (((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM )=[YYYYMM]) AND ((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE)=[DISTRICT]))

ORDER BY NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID;

 

[vbaInet]

Mike, take note of GalaxiomAtHome's last sentence:

However if Arrears Owed is zero you will get an error.

Meaning, the IIF() condition should be checking the Arrears Owed field and not Arrears_Paid.

You can use Nz() to convert the Nulls to zero.

 

[Mike Hughes]

OK I'll play around with it a little and see if I can make it work. Thanks Mike

 

[vbaInet]

Just let us know how you get on.

 

[Mike Hughes]

"Wrong number of arguments with functionin query expression" Error message when I made these changes. Any suggestions? Thanks Mike

SELECT NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE AS DO, NOLDBA_REF_CASE_WORKER.WORKER_NAME AS NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID AS CASELOAD, Sum(NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.TOTAL_DISTRIBUTED) AS [TOTAL DIST], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES AS [OPEN], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES AS CLOSED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE AS [EST O], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED AS [CURR OWED], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID AS [CURR PAID], IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID]=0,0,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID]/[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED]) AS [% CURR PAID], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED AS [ARR OWED], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID AS [ARR PAID], Nz([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED]=0,0,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]/[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED]) AS [% ARR PAID]FROM NOLDBA_DISTRIB_BY_DIST_RESULT_TBL INNER JOIN NOLDBA_REF_CASE_WORKER ON NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.WORKER_ID = NOLDBA_REF_CASE_WORKER.WORKER_ID
GROUP BY NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE, NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM
HAVING (((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE)=[DISTRICT]) AND ((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM)=[YYYYMM]))
ORDER BY NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID;

 

[vbaInet]

Here's the correct syntax:

Nz([Numerator], 0) / IIF([Divisor] = 0, 1, [Divisor])

 

[Mike Hughes]

I'm sorry for all my questions but I just can't seem to make the query give me the results I want. It would be helpful to me if you could take my query and make changes to it and return it to me. Thanks for all your help! Mike

This is the query I now have
SELECT NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE AS DO, NOLDBA_REF_CASE_WORKER.WORKER_NAME AS NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID AS CASELOAD, Sum(NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.TOTAL_DISTRIBUTED) AS [TOTAL DIST], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES AS [OPEN], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES AS CLOSED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE AS [EST O], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED AS [CURR OWED], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID AS [CURR PAID], IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID]=0,0,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID]/[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED]) AS [% CURR PAID], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED AS [ARR OWED], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID AS [ARR PAID], Nz([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID] , 0) /IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]=0,0,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]/[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED]) AS [% ARR PAID]
FROM NOLDBA_DISTRIB_BY_DIST_RESULT_TBL INNER JOIN NOLDBA_REF_CASE_WORKER ON NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.WORKER_ID = NOLDBA_REF_CASE_WORKER.WORKER_ID
GROUP BY NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE, NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM
HAVING (((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE)=[DISTRICT]) AND ((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM)=[YYYYMM]))
ORDER BY NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID;

 

[vbaInet]

If you look at what I wrote in my previous post, it is not the exactly what you've written.

It is a matter of substituting the right field names in the places highlighted:

Nz([[COLOR=Blue][B]Numerator[/B][/COLOR]], 0) / IIF([[B][COLOR=Red]Divisor[/COLOR][/B]] = 0, 1, [[COLOR=Red][B]Divisor[/B][/COLOR]]) AS [% APR PAID]

 

[Mike Hughes]

Well I don't know if I'll ever understand why that worked, but it did.

Thanks for all your time. Your one smart person when it comes to Access.

Mike

 

[Mike Hughes]

So now I have another problem.

When the percent amounts are caculated they look like
% CURR PAID
1.50654913551264 OR 3683.92
I expected the results to be in this format 1.22%
I have the field property set to percent.

This query is used to populate a report. The field property in the report is also set to Percent , Decimal set to AUTO

Can someone help on this one?

This is the current query

SELECT NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE AS DO, NOLDBA_REF_CASE_WORKER.WORKER_NAME AS NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID AS CASELOAD, Sum(NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.TOTAL_DISTRIBUTED) AS [TOTAL DIST], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES AS [OPEN], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES AS CLOSED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE AS [EST O], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED AS [CURR OWED], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID AS [CURR PAID], Nz([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED],0)/IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID]=0,1,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID]) AS [% CURR PAID], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED AS [ARR OWED], NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID AS [ARR PAID], Nz([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED],0)/IIf([NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]=0,1,[NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID]) AS [% ARR PAID]
FROM NOLDBA_DISTRIB_BY_DIST_RESULT_TBL INNER JOIN NOLDBA_REF_CASE_WORKER ON NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.WORKER_ID = NOLDBA_REF_CASE_WORKER.WORKER_ID
GROUP BY NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE, NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.OPEN_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CLOSED_CASES, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.EST_O_TYPE, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.CURRENT_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_OWED, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.ARREARS_PAID, NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM
HAVING (((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.DO_CODE)=[DISTRICT]) AND ((NOLDBA_DISTRIB_BY_DIST_RESULT_TBL.PERIOD_YYYYMM)=[YYYYMM]))
ORDER BY NOLDBA_REF_CASE_WORKER.WORKER_NAME, NOLDBA_REF_CASE_WORKER.WORKER_ID;

 

[Galaxiom]

Set the format on the control where the number is displayed on forms and reports.

Otherwise, use the Round function in the query.

Round([field], i) where i = the number of decimal places

Being a percentage formatted field you will need i = 4 because it is working with the real number not the displayed format.