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?

I there.. you are not telling us where is your problem.. but if you see your code you have = instead of + in the total4 and total5 if..then...


HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Sorry, typo. The problem is that it will not sub total (Totalgoods) if there are less than 5 items. It runs perfectly with 5, but not less.

Step through your code, my guess is that all of your if's are executing regardless of the number of totals.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

Can't find any, see coding above.

....Couldn't find any what? I didn't qualifiy any porition of my comment as a question.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

I carefully inserted code (I have amended two =s) so that the coding can be seen.

Mmmk, this is going no where fast. As Gonzalo pointed out, you have given us an abitrary problem "Your code only works when there are 5 items" and then have provided this code:

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

And have not really said at which point you are having problems. First up, this code is fundamentally flawed because:

If only your fifth if statement evaluates to true your code would look like this (literally)

Totalgoods = Null + Null + Null + Null + <value>

(Note, you have not stated that if Total3 is not null then Total1 and Total2 will also have a value)

Secondly, you said you were using Access so, I am assuming, this VB is actually VBA somewhere inside an Access form. Personally, instead of creating 5 different IF statements I would use a query such as:

SELECT Sum(ISNULL([Total1], 0) + ISNULL([Total2], 0) + ISNULL([Total3], 0) + ISNULL([Total4],0) + ISNULL([Total5], 0)) From <table> Where <value> = <value>

That will Sum all the columns for you which, IMHO, is more effecient.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

I will try, but first could you explain the last line of your code, do I have to fill in details? - I am very much a beginner. I managed to set up the table and form, but just this part has me stumped.

First i am not a VBA guy so how you actually set this up is a little beyond me. Go here http://p2p.wrox.com/access-vba-80/ which is the Access VBA forum and they can help you better there.

The Sum statement only takes on argument Sum([argument]), so if you have a table with 3 rows where the column myColumn is 1, 2, 3: SELECT Sum(myColumn) from Table will return 6 which is the sum of the that column from all 3 rows.

In my example I have used the + operator to give you the sum of all 5 columns assuming that [Total1], 2, 3, etc are actually columns.

As I said earlier, you may want to post this in the VBA thread for better help.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========