one field pulling another field's value, if the first field's valu

[Guest]

Hi,
I know you can have a statement that diaplay's a text string if a field's
value meets the conditions specified, but I can't seem to figure out how to
do what I'm trying to do. If Field A has a value of zero (Field A is a
calculated field), then I want it to use Field B's value instead (Field B is
pulling it's value directly from a table). I've tried writing expressions in
Field A's control source, and I've tried building a macro to set the value of
Field A with Field B's value, if Field A = zero, but nothing has worked. Is
this even possible????


Thanks.

 

[Guest]

Yes, it is possible. You can use coding similar to the following in the
OnOpen event of the report.

Dim FieldA as Integer
Dim FieldB as Integer
Dim FieldC as Integer

With Me.txtTeextBoxName
If IsNull(FieldA)=True Then
FieldC = FieldB
Else
FieldC = FieldA
End If
End With
Me.txtTextBox = FieldC

The logic here is to assign the value you want to a third field (FieldC)
that is not in a table. After you exit the value checking assign the value of
the third field to the text box where you want the value.

HTH
scruffy

 

[Steve Schapel]

AFG,

It is certainly possible to do what you want. It is not clear whether
the calculation that produces the Field A value is performed in a
control on the report, or in the report's underlying query. Nor is it
clear where you want this result to be output to. Nor is it clear what
you have tried so far. If I assume that you want the result to be shown
in a textbox on the report, and that the Field A is in the query, then
this should work as the Control Source of a textbox...
=IIf([Field A]=0,[Field B],0)
Note that the name of this textbox should not be the same as the name as
a field in the report's record source.

 

[Guest]

Thank you for your response. I put in the code you suggested, and it
gives me an error saying that I can't assign a value to the new object (the
new text box). Is there something I'm missing???



Thanks,
Kimberley

 

[Duane Hookom]

I think this is what you want for a control source. I can't be sure since
you didn't specifically state what you want to display if Field A <> 0. You
also suggest Field A is a calculated "field" which suggests this is done in
the query as opposed to a calculated control which would be created in the
report.

=IIf([Field A]=0,[Field B],[Field A])

Make sure the name of the control is not the name of a field in your
report's record source.

 

[Guest]

Thank you very much!!! It worked!!! The only problem now is that I can't
get this new field to SUM in my group footer... any suggestions???

I think this is what you want for a control source. I can't be sure since
you didn't specifically state what you want to display if Field A <> 0. You
also suggest Field A is a calculated "field" which suggests this is done in
the query as opposed to a calculated control which would be created in the
report.

=IIf([Field A]=0,[Field B],[Field A])

Make sure the name of the control is not the name of a field in your
report's record source.

--
Duane Hookom
MS Access MVP

Hi,
I know you can have a statement that diaplay's a text string if a field's
value meets the conditions specified, but I can't seem to figure out how
to
do what I'm trying to do. If Field A has a value of zero (Field A is a
calculated field), then I want it to use Field B's value instead (Field B
is
pulling it's value directly from a table). I've tried writing expressions
in
Field A's control source, and I've tried building a macro to set the value
of
Field A with Field B's value, if Field A = zero, but nothing has worked.
Is
this even possible????


Thanks.

 

[Duane Hookom]

"can't get this new field" is not exactly correct. This is not a "field", it
is a "control". You can't sum controls but you can sum expressions. Try:
=Sum( Val( IIf([Field A]=0,[Field B],[Field A]) ) )

--
Duane Hookom
MS Access MVP

Thank you very much!!! It worked!!! The only problem now is that I can't
get this new field to SUM in my group footer... any suggestions???

I think this is what you want for a control source. I can't be sure since
you didn't specifically state what you want to display if Field A <> 0.
You
also suggest Field A is a calculated "field" which suggests this is done
in
the query as opposed to a calculated control which would be created in
the
report.

=IIf([Field A]=0,[Field B],[Field A])

Make sure the name of the control is not the name of a field in your
report's record source.

--
Duane Hookom
MS Access MVP

Hi,
I know you can have a statement that diaplay's a text string if a
field's
value meets the conditions specified, but I can't seem to figure out
how
to
do what I'm trying to do. If Field A has a value of zero (Field A is a
calculated field), then I want it to use Field B's value instead (Field
B
is
pulling it's value directly from a table). I've tried writing
expressions
in
Field A's control source, and I've tried building a macro to set the
value
of
Field A with Field B's value, if Field A = zero, but nothing has
worked.
Is
this even possible????


Thanks.

 

[Guest]

That makes sense! Thank you so much - it works perfectly.

"can't get this new field" is not exactly correct. This is not a "field", it
is a "control". You can't sum controls but you can sum expressions. Try:
=Sum( Val( IIf([Field A]=0,[Field B],[Field A]) ) )

--
Duane Hookom
MS Access MVP

Thank you very much!!! It worked!!! The only problem now is that I can't
get this new field to SUM in my group footer... any suggestions???

I think this is what you want for a control source. I can't be sure since
you didn't specifically state what you want to display if Field A <> 0.
You
also suggest Field A is a calculated "field" which suggests this is done
in
the query as opposed to a calculated control which would be created in
the
report.

=IIf([Field A]=0,[Field B],[Field A])

Make sure the name of the control is not the name of a field in your
report's record source.

--
Duane Hookom
MS Access MVP

Hi,
I know you can have a statement that diaplay's a text string if a
field's
value meets the conditions specified, but I can't seem to figure out
how
to
do what I'm trying to do. If Field A has a value of zero (Field A is a
calculated field), then I want it to use Field B's value instead (Field
B
is
pulling it's value directly from a table). I've tried writing
expressions
in
Field A's control source, and I've tried building a macro to set the
value
of
Field A with Field B's value, if Field A = zero, but nothing has
worked.
Is
this even possible????


Thanks.

 

[Larry Linson]

Thank you for your response. I put in the
code you suggested, and it gives me an error
saying that I can't assign a value to the new
object (the new text box). Is there something
I'm missing???

The Open Event is too soon to either set or test values of Controls in the
Report -- that's why you got the error message. Others have answered your
original question.

Larry Linson
Microsoft Access MVP