none
Sum Mutliple Text boxes using a Text box RRS feed

  • Question

  • Hello:

    Working in Access Form(Access 2007-2010): I have 5 text boxes (Unbound) that are each populated with a dollar amount(i.e...text193,203,205,207,209). This dollar amount defaults in these fields based on what is selected in 5 related combo boxes. I want the sum of text193,203,205,207,209 to calculate in another unbound text box (text201).

    Expression Builder: (Data/Control Source for text201)

    This worked as long as all the fields had values in them. once any field is blank #Type! error occurs.

    =val([text193])+val([text203])+val([text205])+val([text209])+val([text207])

    This one came out to #Name? error

    =Nz([me].[text193],0)+Nz([me].[text203],0)+Nz([me].[text205],0)+Nz([me].[text209],0)+Nz([me].[text207],0)

    I have tried several other tips I could glean from goggling - lol, but since its all Greek to me I have had no luck. Any tip in layman form would be much appreciated.

    Also have one more struggle: I have the text box formatted to currency but when I go to form view it displays as a text or general look i.e.. 1000 instead of currency $1,000.00 (won't let me post pic, but I do have the cell formatted as currency in property sheet)

     Thank You

    Scott




    • Edited by shildor Thursday, October 13, 2016 12:43 AM
    Thursday, October 13, 2016 12:29 AM

Answers

  • Hi Scott, You might try something along these lines: =CCur(Val(Nz([Text1],0))+Val(Nz([Text2],0))+and so on...) Hope it helps...
    • Proposed as answer by ryguy72 Tuesday, October 18, 2016 2:37 PM
    • Marked as answer by shildor Friday, October 21, 2016 9:25 PM
    Thursday, October 13, 2016 1:20 AM
  • Thank you for the quick response .theDBguy

    You are awesome! worked like a charm.

    =CCur(Val(Nz([Text193],0)))+Val(Nz([Text203],0))+Val(Nz([Text205],0))+Val(Nz([Text209],0))+Val(Nz([Text207],0))

    Thank You

    Scott

    Hi Scott,

    Congratulations! Glad to hear you got it to work. Good luck with your project.

    • Marked as answer by shildor Friday, October 21, 2016 9:25 PM
    Saturday, October 15, 2016 3:35 AM

All replies

  • Hi Scott, You might try something along these lines: =CCur(Val(Nz([Text1],0))+Val(Nz([Text2],0))+and so on...) Hope it helps...
    • Proposed as answer by ryguy72 Tuesday, October 18, 2016 2:37 PM
    • Marked as answer by shildor Friday, October 21, 2016 9:25 PM
    Thursday, October 13, 2016 1:20 AM
  • Hi shildor,

    According to your description, I have made a sample to try to reproduce this issue, unfortunately, I am not able. So I suggest that you could check TextBox name and verify your account to post screenshot, refer to 

    How to Verify Your MSDN/TechNet Forums Account So that You Can Post Images and Links

    Thanks for your understanding.
    Friday, October 14, 2016 2:03 AM
  • Thank you for the quick response .theDBguy

    You are awesome! worked like a charm.

    =CCur(Val(Nz([Text193],0)))+Val(Nz([Text203],0))+Val(Nz([Text205],0))+Val(Nz([Text209],0))+Val(Nz([Text207],0))

    Thank You

    Scott

    • Proposed as answer by ryguy72 Tuesday, October 18, 2016 2:37 PM
    Friday, October 14, 2016 9:25 PM
  • Thank you for the quick response .theDBguy

    You are awesome! worked like a charm.

    =CCur(Val(Nz([Text193],0)))+Val(Nz([Text203],0))+Val(Nz([Text205],0))+Val(Nz([Text209],0))+Val(Nz([Text207],0))

    Thank You

    Scott

    Hi Scott,

    Congratulations! Glad to hear you got it to work. Good luck with your project.

    • Marked as answer by shildor Friday, October 21, 2016 9:25 PM
    Saturday, October 15, 2016 3:35 AM