Problem with calculations on a Datasheet

[mooseless05]

Hi, I have set up a new datasheet for an invoicing sytem I am currently working on - I have the following fields for four calculations I need to do



Quantity
UnitPrice
NetAmount
VATRate
VATAmount

TotalNetAmount
TotalVATAmount

The first calculation is Quantity x Unit Price

so in the "NetAmount" text box's Control Source I entered

=[Quantity]*[UnitPrice]

which works fine and gives me the correct total in the "NetAmount" Box

Then to add VAT I added the following to the "VATAmount" Text box's Control Source

=CLng([NetAmount]*[VATRate]*100)/100

which also works fine and gives me the correct total in the "VATAmount" Box

Now I need to add the two seperate amounts together so I have two text boxes in the Form Footer - One called "TotalNetAmount" and the other called "TotalVATAmount"

For the "TotalNetAmount" I added the following to the Control Source

=Sum([Quantity]*[UnitPrice])

Which again works correctly

but it is with the "TotalVATAmount" I am having the problems - no matter what I try I get either an error, nothing or just 0.

Any sugestions would be much appreciated

Many Thanks

James

Windows XP
Office 2003

[RuralGuy]

Try putting your calculations in the RecordSource instead. It is a query right?

[mooseless05]

Hi,

No it is not a query it is a standard datasheet, therefore, only control source is available.

Thanks

[RuralGuy]

When you say "it is a standard datasheet", are you saying the Datasheet view of a table rather than the Datasheet view of a Form? A Form in any mode has a RecordSource.

[mooseless05]

Hi, it is a Datasheet view of a table - The datasheet form itself has the Record Source set as the Table of the same name.

Is there any way that the actual table can run the caluclations, I'm not sure if this is possible?

Basically all I need is the following fields

Invoice ID - Qty - Details - Unit Price - Net Amount - VAT Rate - VAT Amount

The Net Amount = Qty x Unit Price

The VAT Amount = Net Amount x VAT Rate

Then I need the Total of The Net Amount and the VAT Amount for all the records displayed in the invoice.

If you have any ideas as to how else I can get this to work I would be gratefull.

Thanks

James

[RuralGuy]

I do not know why you have a reluctance to using a query. It takes up essentially no room in the db other than a few character spaces and has a ton of power. Tables are for storing data and query are for calculations. A form cannot distinguish between a table and a query.

[mooseless05]

HI, The reason that I havn't used a query is that I need to enter the data directly into the datasheet - i.e.: I manually enter the quantity, details and unit price and the calculations do the rest - I don't think that a query has that sort of capability - but I could be wrong - I don't have much experience of working with queries so It may well be possible but I'm not sure.

Thanks

James

[RuralGuy]

Look at the Datasheet view of the query. It looks the same as the table. You do not what users to be able to get to anything but forms.