qurey related

[vg]

have defined a query for obtaining "Final Bill Accounted" like this:
Final Bill Accounted: CCur(([Party Payments].[Final Bill Value])-([Party
Payments].[Audit Deductions])-([Party Payments].[Other Misc Deductions]))

But this is not working. I want to deduct 'Audit Deductions & Other Misc
Deductions' from "Final Bill Value" to obtain "Final Bill Accounted".

All the three are defined in Table called "Party Payments".

 

[John Spencer]

"this is not working" is a poor description of the problem. I will
guess that you mean that you are getting blank results for at least some
of the values when you expect to see a number.

If you are getting wrong results or results with too many decimal places
or some other outcome, then you need to tell us in more detail what the
problem is.

Try using the NZ function to force null values to zero. Why? Because if
any value in a calculation is null then the entire equation will return
null. This makes sense if you think of null as "undetermined". If you
add 2 and some "undetermined" number, you have no idea what the result is.

So, if I have guessed your problem correctly, you might try the
following expression:

Final Bill Accounted: CCur(NZ([Party Payments].[Final Bill Value],0)
-NZ([Party Payments].[Audit Deductions],0)-
Nz([Party Payments].[Other Misc Deductions],0))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[vg]

Sir,

Thank U very much. It worked.

"this is not working" is a poor description of the problem. I will
guess that you mean that you are getting blank results for at least some
of the values when you expect to see a number.

If you are getting wrong results or results with too many decimal places
or some other outcome, then you need to tell us in more detail what the
problem is.

Try using the NZ function to force null values to zero. Why? Because if
any value in a calculation is null then the entire equation will return
null. This makes sense if you think of null as "undetermined". If you
add 2 and some "undetermined" number, you have no idea what the result is.

So, if I have guessed your problem correctly, you might try the
following expression:

Final Bill Accounted: CCur(NZ([Party Payments].[Final Bill Value],0)
-NZ([Party Payments].[Audit Deductions],0)-
Nz([Party Payments].[Other Misc Deductions],0))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

have defined a query for obtaining "Final Bill Accounted" like this:
Final Bill Accounted: CCur(([Party Payments].[Final Bill Value])-([Party
Payments].[Audit Deductions])-([Party Payments].[Other Misc Deductions]))

But this is not working. I want to deduct 'Audit Deductions & Other Misc
Deductions' from "Final Bill Value" to obtain "Final Bill Accounted".

All the three are defined in Table called "Party Payments".