Nested if condition in continouous form to get value

LISA GEORGE
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & 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)

Open in new window

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
LISA GEORGEMICROSOFT DEVELOPER

Author

Commented:
thank you John for your time and help. appreciate your suggestion
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

Open in new window

CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

LISA GEORGEMICROSOFT DEVELOPER

Author

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

Author

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!
LISA GEORGEMICROSOFT DEVELOPER

Author

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
Dale FyeOwner, 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

Open in new window

Dale FyeOwner, 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

Open in new window

John TsioumprisSoftware & Systems Engineer

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

Author

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

Author

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
John TsioumprisSoftware & Systems Engineer

Commented:
txt_PayItemCost.Visible = Not IsNull(CostA)

Open in new window

John TsioumprisSoftware & 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...
Clipboard02.jpgClipboard01.jpg
LISA GEORGEMICROSOFT DEVELOPER

Author

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

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial