Total Formula (1 Viewer)

[louisa]

Hello i am very new to access but wondered if there is a way to add a formula in the database. I have 6 text box's which will all contain figures and i wanted it to total them all up and display at the bottom. Can this be done? Many thanks

 

[John Big Booty]

In an unbound text box on your form put

=Tbox1+Tbox2+Tbox3 etc.

 

[louisa]

thanks for the response i tried what you advised but it put every figure from the boxes in to the unbound text box not the total. There are 7 boxes which i want to total but on occasions not all will be completed so i just want it to total all boxes that have been entered but the way you advised it put every figure from every box in the total????

 

[louisa]

Please please help i only need to resolve this and the error OpenForm was cancelled and my database will finally be complete.

 

[louisa]

All i need to do is this now and db finished yeppee!! any ideas on how i create this?

 

[vbaInet]

=Val(txtbox1 & "") + Val(txtbox2 & "") + Val(txtbox3 & "") .... etc

It will throw an error for non numeric data so you will need to handle that too.

 

[louisa]

i tried that and it didnt work :-(

 

[SOS]

Actually, I think you're looking for

=Sum(Nz([Box1],0) + Nz([Box2],0)) ...etc.

 

[John Big Booty]

Please please help i only need to resolve this and the error OpenForm was cancelled and my database will finally be complete.

Sorry I can only respond when I'm on-line which I wasn't at mid night local time

However it looks as if others have chipped in, in my absence

 

[neileg]

Some general advice:
When you want to sum any values there are two common things that go wrong - you have a text datatype in one or more of your fields or you have a null value. Val() turns text into numeric and Nz() turns nulls into zeros. That's why they've been suggested.

There is an alternative way of calculating the sum and that's to include it in the query on which your form is based, providing your text boxes correspond with fileds in the query. In theory this is less efficient than doing it in the form but in practice I've never noticed the difference. I prefer this method as it means that the calculated value is there if you want to use the query to drive a report or another form. You still get the problems with nulls and text, though.

=Sum(Nz([Box1],0) + Nz([Box2],0)) ...etc.

Although this code will run, the SUM part is redundant if you've used the + operator. You only need the SUM if you have a comma separated list.

 

[SOS]

Although this code will run, the SUM part is redundant if you've used the + operator. You only need the SUM if you have a comma separated list.

No, you need the SUM part if you have the control in the FOOTER (which is what the OP asked:

i wanted it to total them all up and display at the bottom

 

[Rich]

No, you need the SUM part if you have the control in the FOOTER (which is what the OP asked:

According to the OP it's only the total of the six boxes which has to be displayed in the footer which does not require the Sum

 

[vbaInet]

It's not a sum of a records, but a sum of values within text boxes SOS.

 

[SOS]

Perhaps the OP can post a screenshot of what they are talking about so there is no doubt.

 

[vbaInet]

The OP hasn't posted in a while, hmm... hehe! It would definitely help if the poster could clarify.

 

[louisa]

Hi everyone, thanks for all your replies.
I have six text boxes all in a downward line. I will be placing figures in the text boxes and i have created another text box underneath them all. Once i place figures in to the box i want it to add them up and place the total in the last box i have created????

 

[vbaInet]

Please zip, upload and attach your db and I'll take a look. A brief description of which form to open etc would help too

 

[Rich]

Then the the controlsource of your last textbox is just
=Nz([Box1],0) + Nz([Box2],0) ...etc.

 

[louisa]

Hi i have attached a copy. Once you open the db it should take you to the General Tab. If you switch to the next one which is lines and click open form you will see a selection of boxes. Where is states Set-up Price and Monthly price i want a box underneath both that states total and on adding data it will automatically add them and place the total in the box. Hope you can help

 

[vbaInet]

Here you go louisa, just copy and paste this in:

Option Compare Database
Option Explicit

Private Sub CalculateTotals()

    Dim ctlTotal As Long, i As Integer
    
    ctlTotal = IIf(IsNumeric(SetupPrice), SetupPrice, 0)

    For i = 1 To 6
        ctlTotal = ctlTotal + IIf(IsNumeric(Me.Controls("SetupPrice" & i).Value), Me.Controls("SetupPrice" & i).Value, 0)
    Next
    Text89 = ctlTotal
End Sub

Private Sub SetupPrice_AfterUpdate()
    Call CalculateTotals
End Sub

Private Sub SetupPrice1_AfterUpdate()
    Call CalculateTotals
End Sub

Private Sub SetupPrice2_AfterUpdate()
    Call CalculateTotals
End Sub

Private Sub SetupPrice3_AfterUpdate()
    Call CalculateTotals
End Sub

Private Sub SetupPrice4_AfterUpdate()
    Call CalculateTotals
End Sub

Private Sub SetupPrice5_AfterUpdate()
    Call CalculateTotals
End Sub

Private Sub SetupPrice6_AfterUpdate()
    Call CalculateTotals
End Sub