Creating an Iif statement within a subform

[Yvette]

Hello,
I'm not sure how to build this formula and would please like some help or
direction on how to do it. Below is the logic of what I want to create, I
get a circular reference error because I'm trying to put this If statement in
one of the result field and it's being referenced in the formula. These
field are located in a subform that's visible in a master form.

Iif ([BalanceForward = 0],[BalanceForward = FinalCap -
ApprovedCap]),([FinalCap = ApprovedCap + BalanceForward])

I would like to see this result as soon as a add a new record (or property)
to the subform. I appreciate any help you can give me. Thank you!

 

[Klatuu]

The IIf statement will not work like that. It only returns one value based
on the evaluation of the first argument. You will need to use an If Then
Else to accomplish this:
Iif BalanceForward = 0 Then
BalanceForward = FinalCap - ApprovedCap
Else
FinalCap = ApprovedCap + BalanceForward
End If

Also, your bracketing in your original IIf is wrong. Are you using the IIf
in VBA or is it in a Query definition? If it is a Query definition, the If
Then Else will not work.

 

[Yvette]

Thanks Klatuu. Sorry, I don't know enough about writing this code to answer
your question, but I am using the expression builder in the Balance Forward
control source field. I do not see the If Then Else statement listed as a
function, so I'm assuming it will not work in this environment as you said.
But your logical statement is what I am looking to do. Any suggestions on
how to accomplish this? Thanks a bunch!

The IIf statement will not work like that. It only returns one value based
on the evaluation of the first argument. You will need to use an If Then
Else to accomplish this:
Iif BalanceForward = 0 Then
BalanceForward = FinalCap - ApprovedCap
Else
FinalCap = ApprovedCap + BalanceForward
End If

Also, your bracketing in your original IIf is wrong. Are you using the IIf
in VBA or is it in a Query definition? If it is a Query definition, the If
Then Else will not work.

Hello,
I'm not sure how to build this formula and would please like some help or
direction on how to do it. Below is the logic of what I want to create, I
get a circular reference error because I'm trying to put this If statement in
one of the result field and it's being referenced in the formula. These
field are located in a subform that's visible in a master form.

Iif ([BalanceForward = 0],[BalanceForward = FinalCap -
ApprovedCap]),([FinalCap = ApprovedCap + BalanceForward])

I would like to see this result as soon as a add a new record (or property)
to the subform. I appreciate any help you can give me. Thank you!

 

[Klatuu]

Your control source for the Balance Forward field in your recordset should
just be the name of the field; otherwise, it will not be a bound control. I
don't know exacly how your form operates, but usually you would do something
like what you are wanting to do in the Before Update event of the subform.
The correct way to do that then would be to assign the values to the controls
on the form and the fields will then be updated when the update completes.

Iif Nz(Me.BalanceForward,0) = 0 Then
Me.BalanceForward = Nz(Me.FinalCap,0) - Nz(Me.ApprovedCap,0)
Else
Me.FinalCap = Nz(ApprovedCap,0) + Nz(BalanceForward,0)
End If

The Nz function will prevent an incorrect value being returned if any of the
fields have nothing entered in them.

Thanks Klatuu. Sorry, I don't know enough about writing this code to answer
your question, but I am using the expression builder in the Balance Forward
control source field. I do not see the If Then Else statement listed as a
function, so I'm assuming it will not work in this environment as you said.
But your logical statement is what I am looking to do. Any suggestions on
how to accomplish this? Thanks a bunch!

The IIf statement will not work like that. It only returns one value based
on the evaluation of the first argument. You will need to use an If Then
Else to accomplish this:
Iif BalanceForward = 0 Then
BalanceForward = FinalCap - ApprovedCap
Else
FinalCap = ApprovedCap + BalanceForward
End If

Also, your bracketing in your original IIf is wrong. Are you using the IIf
in VBA or is it in a Query definition? If it is a Query definition, the If
Then Else will not work.

Hello,
I'm not sure how to build this formula and would please like some help or
direction on how to do it. Below is the logic of what I want to create, I
get a circular reference error because I'm trying to put this If statement in
one of the result field and it's being referenced in the formula. These
field are located in a subform that's visible in a master form.

Iif ([BalanceForward = 0],[BalanceForward = FinalCap -
ApprovedCap]),([FinalCap = ApprovedCap + BalanceForward])

I would like to see this result as soon as a add a new record (or property)
to the subform. I appreciate any help you can give me. Thank you!

 

[Yvette]

Thank you Klatuu for educating me on this. I have used the After Update in
the past to accomplish another task and forgot about it. I will do as you
suggested. Thank you for your expertise guidance. I'll let you know what
the outcome is. = )

Your control source for the Balance Forward field in your recordset should
just be the name of the field; otherwise, it will not be a bound control. I
don't know exacly how your form operates, but usually you would do something
like what you are wanting to do in the Before Update event of the subform.
The correct way to do that then would be to assign the values to the controls
on the form and the fields will then be updated when the update completes.

Iif Nz(Me.BalanceForward,0) = 0 Then
Me.BalanceForward = Nz(Me.FinalCap,0) - Nz(Me.ApprovedCap,0)
Else
Me.FinalCap = Nz(ApprovedCap,0) + Nz(BalanceForward,0)
End If

The Nz function will prevent an incorrect value being returned if any of the
fields have nothing entered in them.

Thanks Klatuu. Sorry, I don't know enough about writing this code to answer
your question, but I am using the expression builder in the Balance Forward
control source field. I do not see the If Then Else statement listed as a
function, so I'm assuming it will not work in this environment as you said.
But your logical statement is what I am looking to do. Any suggestions on
how to accomplish this? Thanks a bunch!

The IIf statement will not work like that. It only returns one value based
on the evaluation of the first argument. You will need to use an If Then
Else to accomplish this:
Iif BalanceForward = 0 Then
BalanceForward = FinalCap - ApprovedCap
Else
FinalCap = ApprovedCap + BalanceForward
End If

Also, your bracketing in your original IIf is wrong. Are you using the IIf
in VBA or is it in a Query definition? If it is a Query definition, the If
Then Else will not work.


:

Hello,
I'm not sure how to build this formula and would please like some help or
direction on how to do it. Below is the logic of what I want to create, I
get a circular reference error because I'm trying to put this If statement in
one of the result field and it's being referenced in the formula. These
field are located in a subform that's visible in a master form.

Iif ([BalanceForward = 0],[BalanceForward = FinalCap -
ApprovedCap]),([FinalCap = ApprovedCap + BalanceForward])

I would like to see this result as soon as a add a new record (or property)
to the subform. I appreciate any help you can give me. Thank you!

 

[Klatuu]

In this case, you need to use the Before Update event. It occurs before
updates are made to the recordset. If you used the After Update event, the
changes you make in your code would not get into the table.

Thank you Klatuu for educating me on this. I have used the After Update in
the past to accomplish another task and forgot about it. I will do as you
suggested. Thank you for your expertise guidance. I'll let you know what
the outcome is. = )

Your control source for the Balance Forward field in your recordset should
just be the name of the field; otherwise, it will not be a bound control. I
don't know exacly how your form operates, but usually you would do something
like what you are wanting to do in the Before Update event of the subform.
The correct way to do that then would be to assign the values to the controls
on the form and the fields will then be updated when the update completes.

Iif Nz(Me.BalanceForward,0) = 0 Then
Me.BalanceForward = Nz(Me.FinalCap,0) - Nz(Me.ApprovedCap,0)
Else
Me.FinalCap = Nz(ApprovedCap,0) + Nz(BalanceForward,0)
End If

The Nz function will prevent an incorrect value being returned if any of the
fields have nothing entered in them.

Thanks Klatuu. Sorry, I don't know enough about writing this code to answer
your question, but I am using the expression builder in the Balance Forward
control source field. I do not see the If Then Else statement listed as a
function, so I'm assuming it will not work in this environment as you said.
But your logical statement is what I am looking to do. Any suggestions on
how to accomplish this? Thanks a bunch!

:

The IIf statement will not work like that. It only returns one value based
on the evaluation of the first argument. You will need to use an If Then
Else to accomplish this:
Iif BalanceForward = 0 Then
BalanceForward = FinalCap - ApprovedCap
Else
FinalCap = ApprovedCap + BalanceForward
End If

Also, your bracketing in your original IIf is wrong. Are you using the IIf
in VBA or is it in a Query definition? If it is a Query definition, the If
Then Else will not work.


:

Hello,
I'm not sure how to build this formula and would please like some help or
direction on how to do it. Below is the logic of what I want to create, I
get a circular reference error because I'm trying to put this If statement in
one of the result field and it's being referenced in the formula. These
field are located in a subform that's visible in a master form.

Iif ([BalanceForward = 0],[BalanceForward = FinalCap -
ApprovedCap]),([FinalCap = ApprovedCap + BalanceForward])

I would like to see this result as soon as a add a new record (or property)
to the subform. I appreciate any help you can give me. Thank you!