I am using Access 2007. I am trying to create an invoice using a table (sales) and form (invoice). Each item is totalled using Total1 = Price1*Quantity1 through to Total5 = etc. I then sub-total (Totalgoods), add on carriage, calcultate VAT and Grandtotal. This works perfectly provided there are 5 items, otherwise it will not sub-total. I have tried various permutations of the following, but without success.

(Private Sub Totalgoods_GotFocus()
Dim Totalgoods
Totalgoods = [Total1]
If Not IsNull([Total2]) Then
Totalgoods = [Total1] + [Total2]
End If
If Not IsNull([Total3]) Then
Totalgoods = [Total1] + [Total2] + [Total3]
End If
If Not IsNull([Total4]) Then
Totalgoods = [Total1] + [Total2] = [Total3] + [Total4]
End If
If Not IsNull([Total5]) Then
Totalgoods = [Total1] + [Total2] + [Total3] = [Total4] + [Total5]
End If
End Sub

Can anybody help?

This works fine

Private Sub()
Dim Totalgoods


If IsNull([Total1]) Or ([Total1]) = "" Then
    [Total1] = 0
End If
    If IsNull([Total2]) Or ([Total2]) = "" Then
        [Total2] = 0
     End If
        If IsNull([Total3]) Or ([Total3]) = "" Then
            [Total3] = 0
        End If
            If IsNull([Total4]) Or ([Total4]) = "" Then
                [Total4] = 0
            End If
                If IsNull([Total5]) Or ([Total5]) = "" Then
                    [Total5] = 0
                End If

Totalgoods = [Total1] + [Total2] + [Total3] + [Total4] + [Total5]

End Sub

I see you break it up sometimes with a =

if that is not a typo then you should

Make more options of the totalgoods to direct them in the calculation format you desire, example:

If Total3 = 0 Then TG = T2 + T4
 If Total 5 = 0 Then TG = T3 + T2
   If Total 3 And Total 4 = 0 Then T1 = T2
   End If
  End If
End If

Hope it helps,

Bjorn

I tried this, but get error message "expecting identifier"

hmm, my best guess is that you are on a form. And the 5 fields are form fields?

If yes then use (Me.Total1) in stead of ([Total1])
and Me.Total1 iso [Total1)

The [Total] are table or Query fields and if u want to use that u must
set the table or query you want vba to use. But I thought you had that done, thus gave just a code snipper.

If the field type is currency or number, then this is called for:

If IsNull([Total1]) Or ([Total1]) = 0 Then
    [Total1] = 0
End If

However, this is setting the field value in the table to 0, which should be the default value of the field. I would go back to the table design and set the default values of Price1, 2, 3, etc and Quantity1, 2, 3, etc to 0. Then you can just do the arithmetic without having to check for nulls. The totals will always be 0 if no amounts were entered when the record was created.

I am troubled by the need for Price1, 2, 3, etc since this indicates improper normalization. This is not developed for n number of sales, but 5 only.

HTH



mmcdonal

Look it up at: http://wrox.books24x7.com