Expression Builder issue (1 Viewer)

[Mike Hughes]

=[PA_CASES]+[PM_CASES]+[PN_CASES]+[PF_CASES]

Using the Expression Builder I'm trying to add the numbers across a row.

The problem is [PF_CASES] is NULL and it is causing nothing to be caculated (field comming up blank)

Some months when I run this report the PF_CASES field will not be null so I can't just remove it from the expression.

Can someone tell me what I'm doing wrong?

 

[bob fitz]

Take a look at the Nz() function in the help files.
Maybe you need something like:
PFCASES: Nz([PF_CASES],0)
in your query.

 

[Mike Hughes]

Bob, can you show me where to put that in my query?

SELECT
PA.DO,
NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE AS P,
NOLDBA_INT_CASE_STATUS.CASE_TYPE AS F,
Count(NOLDBA_INT_CASE_STATUS.CASE_ID) AS CASES INTO PF
FROM
NOLDBA_INT_CASE_STATUS INNER JOIN PA ON NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE = PA.DO

GROUP BY PA.DO, NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE, NOLDBA_INT_CASE_STATUS.CASE_TYPE, NOLDBA_INT_CASE_STATUS.CASE_STATUS

HAVING (((NOLDBA_INT_CASE_STATUS.ESTABLISH_TYPE)="P") AND ((NOLDBA_INT_CASE_STATUS.CASE_TYPE)="F") AND ((NOLDBA_INT_CASE_STATUS.CASE_STATUS)="O"));

 

[bob fitz]

Mike,
Sorry, I misread your OP. Try
=[PA_CASES]+[PM_CASES]+[PN_CASES]+Nz([PF_CASES],0)

 

[Mike Hughes]

That didn't work Bob

=[PA_CASES]+[PM_CASES]+[PN_CASES]+Nz([PF_CASES],0)

 

[Mike Hughes]

I'm sorry Bob, it did work after all. Thanks for the help Mike

 

[bob fitz]

Where are you using the expression.