Please Help!

I have a continous subform where the user can enter a value [Actual Amount].
Then there is a control, named [Total], that sums all of those amounts:
Sum([Actual Amount]).

However, there is a case where there may not be any records entered. In
that case, I would like [Total] to equal 0.

I tried
IIf(IsNull([ActualAmount]),0,=Sum([ActualAmount])
IIf(IsNull([ActualAmount]),0,Sum([ActualAmount])
Sum(Nz([ActualAmount]))
Sum(Nz([ActualAmount]),0)

all of these options don't work:don't return an error message or a value.

Please help. Thanks!

Hi,


If there is no record, there will be no member in the group in the first
place to work with. But if there is row in the group, but is just that ALL
the records making the group have their ActualAmount set to null, a possible
solution is to use:

Nz( SUM(actualAmount), 0 )

The last expression you mentioned should also have worked, on the other hand
(you just call Nz multiple times, rather than just once, if Nz if called
outside the SUM). So, I suspect there is a problem elsewhere. Can you post
the relevant SQL statement?




Hoping it may help,
Vanderghast, Access MVP




"awach" wrote in message
news
I have a continous subform where the user can enter a value [Actual
Amount].
Then there is a control, named [Total], that sums all of those amounts:
Sum([Actual Amount]).

However, there is a case where there may not be any records entered. In
that case, I would like [Total] to equal 0.

I tried
IIf(IsNull([ActualAmount]),0,=Sum([ActualAmount])
IIf(IsNull([ActualAmount]),0,Sum([ActualAmount])
Sum(Nz([ActualAmount]))
Sum(Nz([ActualAmount]),0)

all of these options don't work:don't return an error message or a value.

Please help. Thanks!

There are no records in the group. I tried the expression you posted and
still wasn't able to get anything.

What can I do if there are no records?
(It's important to be a zero because in the parent form there is a grand
total and it needs a value to use or I get an error message)

"Michel Walsh" wrote:

Hi,


If there is no record, there will be no member in the group in the first
place to work with. But if there is row in the group, but is just that ALL
the records making the group have their ActualAmount set to null, a possible
solution is to use:

Nz( SUM(actualAmount), 0 )

The last expression you mentioned should also have worked, on the other hand
(you just call Nz multiple times, rather than just once, if Nz if called
outside the SUM). So, I suspect there is a problem elsewhere. Can you post
the relevant SQL statement?




Hoping it may help,
Vanderghast, Access MVP




"awach" wrote in message
news
I have a continous subform where the user can enter a value [Actual
Amount].
Then there is a control, named [Total], that sums all of those amounts:
Sum([Actual Amount]).

However, there is a case where there may not be any records entered. In
that case, I would like [Total] to equal 0.

I tried
IIf(IsNull([ActualAmount]),0,=Sum([ActualAmount])
IIf(IsNull([ActualAmount]),0,Sum([ActualAmount])
Sum(Nz([ActualAmount]))
Sum(Nz([ActualAmount]),0)

all of these options don't work:don't return an error message or a value.

Please help. Thanks!

Hi,


Any arithmetic operation occurs only on existing records.

If there is no record, but you have a table that old the desired 'rows',
make an outer join:


SELECT b.desiredgroup, Nz(a.FieldNameWIthTheSum)
FROM myquery AS a RIGHT JOIN tableWithALLdesiredRows as b
ON a.fieldMakingGroup = b.desiredRowValue



Example, if tableWIthAllDesiredRow has its field f1:

f1 ' field name
"a"
"b"
"c" ' data value


and if your query only produces rows for "a" and "c", no "b":

myQuery
country, mySum
"a", 1101
"c", 2011


then



SELECT b.f1, Nz(a.mySum)
FROM myquery AS a RIGHT JOIN tableWithALLdesiredRows as b
ON a.country= b.f1


will add

"b", 0


to the result.



Hoping it may help,
Vanderghast, Access MVP


"awach" wrote in message
...
There are no records in the group. I tried the expression you posted and
still wasn't able to get anything.

What can I do if there are no records?
(It's important to be a zero because in the parent form there is a grand
total and it needs a value to use or I get an error message)

"Michel Walsh" wrote:

Hi,


If there is no record, there will be no member in the group in the first
place to work with. But if there is row in the group, but is just that
ALL
the records making the group have their ActualAmount set to null, a
possible
solution is to use:

Nz( SUM(actualAmount), 0 )

The last expression you mentioned should also have worked, on the other
hand
(you just call Nz multiple times, rather than just once, if Nz if called
outside the SUM). So, I suspect there is a problem elsewhere. Can you
post
the relevant SQL statement?




Hoping it may help,
Vanderghast, Access MVP




"awach" wrote in message
news
I have a continous subform where the user can enter a value [Actual
Amount].
Then there is a control, named [Total], that sums all of those amounts:
Sum([Actual Amount]).

However, there is a case where there may not be any records entered.
In
that case, I would like [Total] to equal 0.

I tried
IIf(IsNull([ActualAmount]),0,=Sum([ActualAmount])
IIf(IsNull([ActualAmount]),0,Sum([ActualAmount])
Sum(Nz([ActualAmount]))
Sum(Nz([ActualAmount]),0)

all of these options don't work:don't return an error message or a
value.

Please help. Thanks!

(change "old" to "holds"... in "that old the desired 'rows', ", for sure,
and don't tell anyone else :-) )

V.