# Nested if condition in continouous form to get value

LISA GEORGE used Ask the Experts™
on
Hi all, i have 2 unbound text boxes costA and CostB and 1 bound text box rate in continuous form. now i am writing vba code to get rate value depending on costa and costb values.
if costa and costb values are null then rate = 0
if costa has value then rate = costa or
if costb has value then rate = costb
please note that in continuous form we will be having either costa or costb value. we don't have both values same time. one value will be always null
whatever code i am trying to modify it is either working for costa or costb but not for both.
If (IsNull(CostB) = True And IsNull(CostA) = True) Then

Rate.Value = 0
ElseIf (IsNull(CostB) = False And IsNull(CostA) = True) Then

Rate.Value = CostB.Value
Else
Rate.Value = CostA.Value

End If

can someone tell this is happening because of continuous form?
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer

Commented:
Unbound Textboxes are not "bound" so everytime you feed them some value this "value" will get propagated to all the records of your Continuous form.
IsNull does return a true or false so no need for testing for value
so
``````If IsNull(CostB)
``````
translates to
If True then
If you need the equality then you could use the Nz
Nz(CostB,0) = 0 <-- If is Null it will return 0 or return the actual value.
Probably sharing a screenshot of what you have will help
MICROSOFT DEVELOPER

Commented:
i just fiugured out what i did wrong
i modified to
If IsNull(CostB) = True Or IsNull(CostA) = True Then
Rate.Value = 0
ElseIf Not IsNull(CostA) = True And CostA <> "" Then
Rate.Value = CostA.Value
Else
Rate.Value = CostB.Value

End If

seems now this is working as expected. fingers cross if no further issues found
once again thank you for your time and help
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can reduce it slightly as Null + somevalue always will be Null:

``````If IsNull(CostB + CostA) Then
Rate.Value = 0
ElseIf Nz(CostA) <> "" Then
Rate.Value = CostA.Value
Else
Rate.Value = CostB.Value
End If
``````
MICROSOFT DEVELOPER

Commented:
thank you gustav..this is also working fine for me
MICROSOFT DEVELOPER

Commented:
thank you guys for your time and help..
i am very thankful to you as you helped me in many issues.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!
MICROSOFT DEVELOPER

Commented:
also i have other doubt which is bit related to this
when i update bound combo box, unbound text boxes costa and costb will be getting values using expression
now i am trying to hide those text boxes if value is null
but when i use vba code costa is still visible
If CostA = 0 Then

CostA.Visible = False
Else
CostA.Visible = True

End If

i tried with isnull(costA) = true as well but it's still visible

i used 0 because in my expression i set it to 0 if no cost is there

what's wrong in that code..please let me know
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Gustav,

Won't

If IsNull(CostB + CostA) Then

be true if CostA is null or CostB is null.

x + NULL = NULL
Null + x = NULL
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You have to both turn Visible on and off:

``````Dim VisibleA As Boolean

VisibleA = (CostA <> 0)

CostA.Visible = VisibleA
CostB.Visible = Not VisibleA
``````
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
``````If NOT IsNull(CostB) Then
Rate.Value = CostB.Value
ElseIf NOT isnull(CostA)  Then
Rate.Value = CostA.Value
Else
Rate.Value = CostB.Value
End If
``````
Software & Systems Engineer

Commented:
Playing with Null is always not a good idea...but points awarded so no point is discussing it....
MICROSOFT DEVELOPER

Commented:
@gustav..that code isnot working for visibility but thank you for your time and help
MICROSOFT DEVELOPER

Commented:
if i use this code then all records unbound text box costa is getting invisible
Dim v As Boolean
v = (IsNull(CostA) = True)
txt_PayItemCost.Visible = v

but i want to hide that text box for that particular row/record in continuous form
Software & Systems Engineer

Commented:
``````txt_PayItemCost.Visible = Not IsNull(CostA)
``````
Software & Systems Engineer

Commented:
You can't control visibility on individual records on Continuous forms...what you can ...is use Conditional formatting and play with Enabled/Disable and fore/background colors,
Also although you have awarded points you should again recheck the "solution" ... as Dale mentioned ...it doesn't work and if you go the path of mixing Nulls with values you will have probably severe issues later...
MICROSOFT DEVELOPER

Commented:
@john thank you for your suggestion. i will try this as work around for that using conditional formatting but i don't know if user will be happy for this or not

Do more with