|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 8th, 2007, 05:15 PM
posted to microsoft.public.access.reports
|
|||
|
|||
Report Sub-Total formulas where a value is null
I have created a crosstab query in which some cells are null. There is
a report based on this query in which I want to create a field that creates a sub-total for the row. As soon as the formula encounters an empty field it returns a null result. What do I have to do to get the formula to recognize the null as a zero? For example [Val1] [Val2] [Val3] Subtotal calculated 2 3 4 9 6 null 8 I want 14, I'm getting a null I'm not having a problem creating the basic formula, I just don't know how to account for the null values. Lydia 
|
|
#2
August 8th, 2007, 05:29 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Sub-Total formulas where a value is null
Nz is your friend - it changes nulls to 0 (or other specified value)
Nz(Val1,0) + Nz(Val2,0) + Nz(Val3,0) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Lydia" wrote in message ups.com... I have created a crosstab query in which some cells are null. There is a report based on this query in which I want to create a field that creates a sub-total for the row. As soon as the formula encounters an empty field it returns a null result. What do I have to do to get the formula to recognize the null as a zero? For example [Val1] [Val2] [Val3] Subtotal calculated 2 3 4 9 6 null 8 I want 14, I'm getting a null I'm not having a problem creating the basic formula, I just don't know how to account for the null values. Lydia
|
|
#3
August 8th, 2007, 07:46 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Sub-Total formulas where a value is null
If I understand correctly, you can create the "row" total in the crosstab
which would be more efficient than creating a complex expression in a control source. -- Duane Hookom Microsoft Access MVP "John Spencer" wrote: Nz is your friend - it changes nulls to 0 (or other specified value) Nz(Val1,0) + Nz(Val2,0) + Nz(Val3,0) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Lydia" wrote in message ups.com... I have created a crosstab query in which some cells are null. There is a report based on this query in which I want to create a field that creates a sub-total for the row. As soon as the formula encounters an empty field it returns a null result. What do I have to do to get the formula to recognize the null as a zero? For example [Val1] [Val2] [Val3] Subtotal calculated 2 3 4 9 6 null 8 I want 14, I'm getting a null I'm not having a problem creating the basic formula, I just don't know how to account for the null values. Lydia
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
