empty continuous form, sum is null

[Jeff A via AccessMonster.com]

hi,

i have a continuous subform that may or may not contain records depending on
a search criteria in the main form. on the footer of the subform, i have a
field that does a Sum on a number field. it works fine if there are records
in the subform. if there are no records, Sum returns null. i tried using Sum
with Nz but it doesn't work. what i would want is for that field to contain 0
if there are no records returned, so i can use it in calculations.

hope anyone could help. thanks.

 

[Rob Oldfield]

nz should be the way to go. How are you using it?

 

[Jeff A via AccessMonster.com]

=Sum(Nz([Amount3],0))

I also tried

= Nz(Sum([Amount3],0))

nz should be the way to go. How are you using it?

hi,

[quoted text clipped - 6 lines]

hope anyone could help. thanks.

 

[Rob Oldfield]

Strange. It seems to get a bit confused about its null values (no change
there then). Try this...

Drop the following function into the form code:

Private Function nz2(n) As Single
nz2 = Nz(n)
End Function

and then change the control's source to =nz2(sum([amount3]))

Note that that doesn't work if you define a data type for n - it needs to be
a variant. And also that you don't need to specify the 0 argument with nz
here - it will default to 0 for numeric values.

=Sum(Nz([Amount3],0))

I also tried

= Nz(Sum([Amount3],0))

nz should be the way to go. How are you using it?

hi,

[quoted text clipped - 6 lines]

hope anyone could help. thanks.

 

[Jeff A via AccessMonster.com]

Ok, i'll try it later. Thanks!

Strange. It seems to get a bit confused about its null values (no change
there then). Try this...

Drop the following function into the form code:

Private Function nz2(n) As Single
nz2 = Nz(n)
End Function

and then change the control's source to =nz2(sum([amount3]))

Note that that doesn't work if you define a data type for n - it needs to be
a variant. And also that you don't need to specify the 0 argument with nz
here - it will default to 0 for numeric values.

=Sum(Nz([Amount3],0))

[quoted text clipped - 9 lines]

 

[Duane Hookom]

If there are no records in the subform, then Nz() won't work since there are
no records to Nz() on. Try something like:
=IIf(HasData, Sum([Amount3]),0)

--
Duane Hookom
MS Access MVP

Ok, i'll try it later. Thanks!

Strange. It seems to get a bit confused about its null values (no change
there then). Try this...

Drop the following function into the form code:

Private Function nz2(n) As Single
nz2 = Nz(n)
End Function

and then change the control's source to =nz2(sum([amount3]))

Note that that doesn't work if you define a data type for n - it needs to
be
a variant. And also that you don't need to specify the 0 argument with nz
here - it will default to 0 for numeric values.

=Sum(Nz([Amount3],0))

[quoted text clipped - 9 lines]

hope anyone could help. thanks.