Correct Nz syntax when summing?

[CW]

On my Job Costing form I have two subforms, one for sales invoices and the
other for cost invoices. These are continuous forms and each has a Total
control in its footer that sums the amounts using =Sum([SalesValue]) and
=Sum([CostValue]).

If no invoices have been entered yet (on either side) I need the Total
control(s) to show 0.00, rather than a blank.

Question 1: I cannot get Nz to do this, could you please confirm how the
Total record source should be expressed?

Then on the main Job Costing form I have 3 controls for Sales, Costs,
Profit/Loss.
The Sales and Costs controls pull from the above-mentioned Totals on the
subforms and obviously the P/L control is calculated from those.
If either of the subform Totals is blank, the relevant main form control
will be blank, as will the Profit/Loss calculated control. I want those to be
0.00, not blanks.

Question 2: Will the correct use of Nz in the subform Totals cure this, or
will I need to add Nz to the main form controls too? And if so, how should I
modify the existing record source which is for example
=[frmCostssubform].[Form]![TotalCosts]

Many thanks
CW

 

[Danny Lesandrini]

The SQL Server equiv to Nz is the ISNULL() function, so this ...
SELECT Nz([SalesValue], 0)

becomes this ...
SELECT ISNULL([SalesValue], 0)

 

[CW]

Er... I don't understand why you mention SQL Server? I am using Access 2003.
CW

The SQL Server equiv to Nz is the ISNULL() function, so this ...
SELECT Nz([SalesValue], 0)

becomes this ...
SELECT ISNULL([SalesValue], 0)


--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

On my Job Costing form I have two subforms, one for sales invoices and the
other for cost invoices. These are continuous forms and each has a Total
control in its footer that sums the amounts using =Sum([SalesValue]) and
=Sum([CostValue]).

If no invoices have been entered yet (on either side) I need the Total
control(s) to show 0.00, rather than a blank.

Question 1: I cannot get Nz to do this, could you please confirm how the
Total record source should be expressed?

Then on the main Job Costing form I have 3 controls for Sales, Costs,
Profit/Loss.
The Sales and Costs controls pull from the above-mentioned Totals on the
subforms and obviously the P/L control is calculated from those.
If either of the subform Totals is blank, the relevant main form control
will be blank, as will the Profit/Loss calculated control. I want those to
be
0.00, not blanks.

Question 2: Will the correct use of Nz in the subform Totals cure this, or
will I need to add Nz to the main form controls too? And if so, how should
I
modify the existing record source which is for example
=[frmCostssubform].[Form]![TotalCosts]

Many thanks
CW

 

[Danny Lesandrini]

Ooops! I had been reading the Access SQL Server ADP newsgroup just before
looking at your post and my mind was still there.

Are you simply asking how to implement the Nz() function? I think I got
confused in your explanation too. This is how you should include the Nz()
function in your control functions.

Sum(Nz([SalesValue], 0))

Sorry about the first post.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Er... I don't understand why you mention SQL Server? I am using Access
2003.
CW

The SQL Server equiv to Nz is the ISNULL() function, so this ...
SELECT Nz([SalesValue], 0)

becomes this ...
SELECT ISNULL([SalesValue], 0)


--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

On my Job Costing form I have two subforms, one for sales invoices and
the
other for cost invoices. These are continuous forms and each has a
Total
control in its footer that sums the amounts using =Sum([SalesValue])
and
=Sum([CostValue]).

If no invoices have been entered yet (on either side) I need the Total
control(s) to show 0.00, rather than a blank.

Question 1: I cannot get Nz to do this, could you please confirm how
the
Total record source should be expressed?

Then on the main Job Costing form I have 3 controls for Sales, Costs,
Profit/Loss.
The Sales and Costs controls pull from the above-mentioned Totals on
the
subforms and obviously the P/L control is calculated from those.
If either of the subform Totals is blank, the relevant main form
control
will be blank, as will the Profit/Loss calculated control. I want those
to
be
0.00, not blanks.

Question 2: Will the correct use of Nz in the subform Totals cure this,
or
will I need to add Nz to the main form controls too? And if so, how
should
I
modify the existing record source which is for example
=[frmCostssubform].[Form]![TotalCosts]

Many thanks
CW

 

[CW]

Thanks Danny, now I see why my attempts didn't work - I wasn't including the
0 at the end!
CW

Ooops! I had been reading the Access SQL Server ADP newsgroup just before
looking at your post and my mind was still there.

Are you simply asking how to implement the Nz() function? I think I got
confused in your explanation too. This is how you should include the Nz()
function in your control functions.

Sum(Nz([SalesValue], 0))

Sorry about the first post.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Er... I don't understand why you mention SQL Server? I am using Access
2003.
CW

The SQL Server equiv to Nz is the ISNULL() function, so this ...
SELECT Nz([SalesValue], 0)

becomes this ...
SELECT ISNULL([SalesValue], 0)


--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



On my Job Costing form I have two subforms, one for sales invoices and
the
other for cost invoices. These are continuous forms and each has a
Total
control in its footer that sums the amounts using =Sum([SalesValue])
and
=Sum([CostValue]).

If no invoices have been entered yet (on either side) I need the Total
control(s) to show 0.00, rather than a blank.

Question 1: I cannot get Nz to do this, could you please confirm how
the
Total record source should be expressed?

Then on the main Job Costing form I have 3 controls for Sales, Costs,
Profit/Loss.
The Sales and Costs controls pull from the above-mentioned Totals on
the
subforms and obviously the P/L control is calculated from those.
If either of the subform Totals is blank, the relevant main form
control
will be blank, as will the Profit/Loss calculated control. I want those
to
be
0.00, not blanks.

Question 2: Will the correct use of Nz in the subform Totals cure this,
or
will I need to add Nz to the main form controls too? And if so, how
should
I
modify the existing record source which is for example
=[frmCostssubform].[Form]![TotalCosts]

Many thanks
CW