Sum Mutliple Text boxes using a Text box

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

Answer 1

Hi Scott, You might try something along these lines: =CCur(Val(Nz([Text1],0))+Val(Nz([Text2],0))+and so on...) Hope it helps...

Answer 2

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.

Answer 3

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

Answer 4

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.