Retrieve a figure then use in calc

[CraigBFG]

I have a sales invoice form which populates from a query which performs certain calculations - pretty simple stuff.

However, i want to make it a bit clever so that I can maintain a "My Company" section which has things like VAT rate etc.

So when the invoice is raised for Joe Bloggs Ltd, the VAT is calculated based on the rate within another table.

However, no matter what I try, I cant get this to work.

Any ideas??

Thanks
Craig

 

[DJN]

Create a new table, tblRate. In the table have the fields you require. e.g. VAT etc. create a query with the first table and the tblRate table. Create a calculated field in the query.

SELECT tblInvoice.Invoice, [Invoice]*[VAT] AS InvoiceAmount
FROM tblInvoice, tblRate;

The two table do not have to be joined, but must be in the query grid

 

[CraigBFG]

Hi, tried that and whilst the calculation works within the query, the form becomes an non-updateable recordset - which I dont want.

Any ideas, heres my query. Its the LabourCISCalc part thats causing the problem.

SELECT tSalesInv.SInvId, tSalesInv.SInvRef, tSalesInv.SInvDate, tSalesInv.CompanyId, tSalesInv.ContractId, tSalesInv.Labour, tSalesInv.LabourCIS, tSalesInv.Materials, tSalesInv.VAT, tSalesInv.SInvTotal, tSalesInv.FAO, tSalesInv.CustRef, CCur([Labour]+[Materials])/100*17.5 AS VATCalc, CCur([Labour]/100*[tMyCompany]![CISRate]) AS LabourCISCalc, [Materials]+[Labour]+[VATCalc]-[LabourCISCalc] AS TotalPayableCalc
FROM tMyCompany, tCompanies INNER JOIN tSalesInv ON tCompanies.CompanyId = tSalesInv.CompanyId;

 

[CraigBFG]

Any ideas anyone???

The method of using an unjoined table within a report works fine, but put this into a calculated query and it aint a workin.

Please help!?!?!?

 

[Pat Hartman]

Jet creates a cartesian product from a non-specific join and a cartesian product is not updatable. If you don't have a common column on which to explicitly join the tables, you'll need to remove the table you just added and revert to a DLookup() or open a recordset in code.