Adding Textbox Amounts

[Gator]

I have about 30 textboxes on a form where only a few textboxes wil have
amounts entered per record. I want to see the total in a textbox at the
botton of the form of the current record, where as each record that is
displayed on the form the textbox at the bottom will show that record's
total. The Textboxes are txt1, txt2, txt3, .....
what can i put in the Total txtbox to sum all of these textboxes or do I
need VB.

 

[Al Campagna]

Gator,
Try...
= NZ(Txt1) + NZ(Txt2) + NZ(Txt3).... NZ((Txt30)
If any Txt* fields are blank or null, it will assume a value of 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Klatuu]

Al, without the second argument in the Nz function, the default is
vbNullstring, not 0.

 

[Gator]

Is there a way to capture all thirty textboxes without having to type in each
one in the control?

 

[Klatuu]

Yes. You could write a function that loops through the controls collection
of the form and add the value if the name starts with txt.

Function GetTxtTotal() As Long (Long just for example, use the type of the
fields)
Dim lngI As Long

For lngI = 1 To 30
GetTxtTotal = GettxtTotal + Nz(Me.Controls("txt" & Cstr(lngI)),0)
Next lngI

End Function

The for the control source of the totals text box:
=GetTxtTotal()

 

[Al Campagna]

You're right. I should have picked up on that.
Thanks for the heads up...
Al

 

[AEROTJK]

Would anyone know how to get around this:

Text1=25
Text2=30

=Nz[Text1]+Nz[Text2]

Result:
2530 and NOT 55

Any thoughts???

 

[John W. Vinson]

Would anyone know how to get around this:

Text1=25
Text2=30

=Nz[Text1]+Nz[Text2]

Result:
2530 and NOT 55

Any thoughts???

The NZ() function requires parentheses, not brackets.

Try

=Val(NZ([Text1],"")) + Val(NZ([Text2],""))

The Val function will convert a string "25" to a numeric 25 (and an empty
string to 0).

 

[AEROTJK]

Much obliged Mr. Vinson, jackpot!!! Thanks for the time!!

Would anyone know how to get around this:

Text1=25
Text2=30

=Nz[Text1]+Nz[Text2]

Result:
2530 and NOT 55

Any thoughts???

The NZ() function requires parentheses, not brackets.

Try

=Val(NZ([Text1],"")) + Val(NZ([Text2],""))

The Val function will convert a string "25" to a numeric 25 (and an empty
string to 0).