How to Calculate a Total (Sum) number

TGoldenEye

order cheap viagra Arakawa and the sequence animators. When that they can’t, there’re possibly up to now coping with cheap cialis prices the actual 1980s. Increases Fertility Many people believe that ginger tea is an aphrodisiac despite its consumption viagra buy usa dampens on your sex life, leaving you and your partner unsatisfied. Now kamagra cheapest levitra oral jelly has provided the benefit for curing this problem without any surgery.


How to Calculate a Total number from Textbox

We can get the total number by adding those numbers together. When we have three numbers, we can find the total by adding all three numbers. In this How To, I will show you how to add three checks and get the total amount of those three checks. I will use the unbound textbox for demonstration.

Step 1 Create a Calculate Form

–  Create a blank form

– Under the form design view, insert four unbound textboxes

–  Name text labels as Check 1, Check 2, Check 3, and Total:

– Click on Unbound textbox and rename it as txtChk1, txtChk2, txtChk3, and txtTotal under the Property Sheet, Other tab.

insert-text-box

Step 2 Insert a button from the Design menu

  • Name a button caption as Calculate
  • Rename a button name as cmdCalculate under the Property Sheet

insert-button

Step 3 Select [Event Procedure] for On Click event under the Property Sheet

calcualte-click



Step 4 Adding the VBA under the On Click event procedure of Calculate button

For example below, I will show you how to use different code with different outcome. You will learn the basic importance of data type with different result.

Adding All Checks without declaration of data type

The Unbound textbox has a default data type as a text. When we add the value of three textboxes together, the result is combining all three textboxes. There is no calculation for the total.

Per a picture below, I enter 10 for check 1, 20 for check 2, 30 for check 3. The total is 102030 as putting all three numbers together in the order. This happened because we did not declare the data type representing those textboxes.

Private Sub cmdCalculate_Click()

Me.txtTotal = Me.txtChk1 + Me.txtChk2 + Me.txtChk3

End Sub

add-text-box

Formatting Unbound Textbox as a Standard number

There is not formatting on the unbound textbox on the picture above as the numbers are located at the left hand side of the box. As we know these unbound textboxes are for the amount of checks to be entered. Under the Property Sheet, select Standard for the Format of all unbound textboxes as shown in the picture below.

standard-format

Declare Data Type as Integer

In this example, I will declare the date type of all textboxes as Integer. I declare variable Chk1 as Integer and assign the value from textbox txtChk1 to variable Chk1. Per code below, I assign the total amount equal to the value of all three textboxes adding together as Me.txtTotal.Value = Chk1 + Chk2 + Chk3.

Private Sub cmdCalculate_Click()
Dim Chk1 As Integer
Dim Chk2 As Integer
Dim Chk3 As Integer
    Chk1 = Me.txtChk1.Value
    Chk2 = Me.txtChk2.Value
    Chk3 = Me.txtChk3.Value
    Me.txtTotal.Value = Chk1 + Chk2 + Chk3
End Sub

Or in short code, I declare a variable total as Integer data type and assign the value of all three textboxes to total variable. Then I assign the value of total to textbox txtTotal as shown below.

Private Sub cmdCalculate_Click()
Dim total As Integer
   total = Me.txtChk1.Value + Me.txtChk2.Value + Me.txtChk3.Value
   Me.txtTotal = total
End Sub

From the code above, the result of adding the value of all three textboxes is 8.00 (2.52+3+2). The correct answer should display as 7.52. It displays the result as 8.00 because the Integer data type cannot handle the decimal number. It can handle only the whole number, so it rounds up to 8.00.

set-as-integer

Declare Data Type as Double

In this example, I declare the date type of all textboxes as Double. I declare variable Chk1, Chk2, and Chk3 as Double and assign the value from all textboxes to those variables. Per code below, I assign the total amount of all three textboxes to the txtTotal textbox as Me.txtTotal.Value = Chk1 + Chk2 + Chk3.

Private Sub cmdCalculate_Click()
Dim Chk1 As Double
Dim Chk2 As Double
Dim Chk3 As Double
    Chk1 = Me.txtChk1.Value
    Chk2 = Me.txtChk2.Value
    Chk3 = Me.txtChk3.Value
    Me.txtTotal.Value = Chk1 + Chk2 + Chk3
End Sub

Or in short code,  I declare a variable total as Double data type and assign the value of all three textboxes to total variable. Then I assign the value of total to textbox txtTotal as shown below.

Private Sub cmdCalculate_Click()
Dim total As Double
    total = Me.txtChk1.Value + Me.txtChk2.Value + Me.txtChk3.Value
    Me.txtTotal = total
End Sub

From the code above, the result of adding the value of all three textboxes is 5.50 (1.5+2+2). The number 5.5 is a correct amount because the Double data type is able to handle the decimal number.

set-double



How to Handle the Error from Null value

Per the picture below, I enter number 12 for Check 1, 10 for Check 2, and leave blank for Check 3. You will see the error message (Invalid use of Null) pop-up after I click Calculate button.

null-on-form

VBA will highlight a line that has an error. In this example, the error part is Me.txtChk3.Value because there is no number entered per picture above. Check 3 box has a null value (nothing). Null is not the same as 0. If Check 3 = 0 then there is no error. The result will be 22.00 (12+10+0).

highlight-code-with-null

To handle the null value, I will use IF Statement to check if there is no null value then keep adding the value until the last amount (Check 3). Per code below, I assign a variable “Total” as Double to handle the decimal number. I use If Statement to check if Check 1 is not null then add the amount of Check 1 to variable Total, if Check 2 is not null then add the amount of Check 2 to variable Total, if Check 3 is not null then add the amount of Check 3 to variable Total. The program will process the Total (sum) only on the check box that has a value.

Private Sub cmdCalculate_Click()
Dim Total As Double
If Not IsNull(Me.txtChk1) Then
     Total = Me.txtChk1.Value
End If
If Not IsNull(Me.txtChk2) Then
     Total = total + Me.txtChk2.Value
End If
If Not IsNull(Me.txtChk3) Then
     Total = total + Me.txtChk3.Value
End If
     Me.txtTotal = Total
End Sub

The Total (sum) of two checks is 21.52 as shown in the picture below. There is no error even there is no value on the Check 3.

solve-null-value



Related posts