I did leave out the NZ, and the expression did not work under all scenarios
Here is the logic I am looking for
1. "if there are no records in the subform 'W/C MS',
use the value that is in the 'Loss Payments' control in the subform 'W/C
CB', and if there are records, use the value that is in the 'Loss
Payments'
control in the subform 'W/C MS'
2. "if there are no records in the subform 'W/C CB',
use the value that is in the 'Loss Payments' control in the subform 'W/C
MS', and if there are records, use the value that is in the 'Loss
Payments'
control in the subform 'W/C MS'
3. "if there are records in the subform 'W/C MS' and if there are records
in the subform 'W/C CB', add the value 'Loss Payments' control from each
form.
There will always be records in either the subform 'W/C MS' or the subform
'W/C CB', or in both, but there is no situation in which both subforms
would
be empty.
I hope this clarifies.
Ken Snell said:
The expression that you posted:
=IIf([W/C MS].[Form].[RecordsetClone].[RecordCount]=0,([W/C
MS].[Form]![Loss
Payments]),([W/C MS].[Form]![Loss Payments],0)+([W/C CB].[Form]![Loss
Payments],0))
makes no sense to me based on the problem that you said you were having.
Your expression above says "if there are no records in the subform 'W/C
MS',
use the value that is in the 'Loss Payments' control in the subform 'W/C
MS', and if there are records, use the value that is in the 'Loss
Payments'
control in the subform 'W/C MS' (the ,0 there has no purpose?) plus the
value that is in the 'Loss Payments' control in the 'W/C CB' subform (the
,0
there also has no purpose). I think you've left out an Nz for the latter
two
portions of the expression?
I do not understand clearly what you want to do. Are you saying that you
want to test the record count of both subforms in order to decide if you
can
use the value from the subform control (from each subform)?
--
Ken Snell
<MS ACCESS MVP>
Thanks. I had to revise the expression as follows. The way it was
previously set up, whenever the [W/C MS] query - subform had not data,
a 0
was always entered and it ignored the second part of the IIf statement.
=IIf([W/C MS].[Form].[RecordsetClone].[RecordCount]=0,([W/C
MS].[Form]![Loss
Payments]),([W/C MS].[Form]![Loss Payments],0)+([W/C CB].[Form]![Loss
Payments],0))
The expression adds field from two seperate subforms, from two seperate
queries. The above formula address when the [W/C MS] query and subform
returns no data. How do I modify the expression to make sure the same
actions take place for the [W/C CB] query and subform as well. I need
the
same type of expression in the same unbound text box that addresses
both
queries and both subforms at the same time. I need some sort of Or in
there.
Again, thanks for your help.
:
Sorry...typo:
=IIf([W/C MS].[Form].RecordsetClone.RecordCount=0, 0,Nz([W/C
MS].[Form]![Loss Payments],0)+Nz([W/C CB].[Form]![Loss Payments],0))
--
Ken Snell
<MS ACCESS MVP>
You are correct in your assumption. I copied and pasted this
expression
in
the control source of the unbound textbox, but got the following
error
message:
The expression is missing a closing parenthesis, bracket (]) or
vertical
bar
(|).
:
Your original post was not specific about how you were trying to
put
the
value into the unbound textbox ... I assumed (perhaps incorrectly)
that
you
were using an expression in the textbox's ControlSource. So I
posted
an
example of an expression to use in that ControlSource.
If you are using another approach, post more details.
--
Ken Snell
<MS ACCESS MVP>
I am new at this so I may need a little additional guidance.
Where am I placing this expression?
:
Because the subform has no data (no records in its
recordsource),
there
is
no control from which to read a value, and the Nz function fails
as
well
without a value.
Test the RecordCount property of the subform's RecordsetClone
instead:
=IIf(([W/C MS].[Form].RecordsetClone.RecordCount=0, 0,Nz([W/C
MS].[Form]![Loss Payments],0)+Nz([W/C CB].[Form]![Loss
Payments],0))
--
Ken Snell
<MS ACCESS MVP>
I have a two subforms (W/C MS & W/C CB) in a master form that
pulls
data
from
a table based upon two seperate queries. At times there is no
data
matching
one or the other queries. On the master form I have an
unbound
field
in
which I am trying to add the loss payments fields from the two
seperate
subforms. I am attempting to use the Nz function and have the
following
expression in the control source of the unbound field:
Nz([W/C MS].[Form]![Loss Payments],0)+Nz([W/C CB].[Form]![Loss
Payments],0)
I still get the #Error message. Any suggestions?
