Create New Field from Totalling Other Fields

[LindaM]

Hi there



I'm new to this Forum, so please forgive me if this has been asked elsewhere.

I am trying to build a query and some of the fields are:

Selling Price
VAT Amount
Discount

The VAT Amount has different amounts - 0 and 15.

I want to create a new field called Total Price which would calculate as follows:

Selling Price + VAT Amount Less Discount

I've tried IIF Statements like:

Total Price: IIf([VAT Amount]=0,[Selling Price]-[Discount],[VAT Amount]/[Selling Price])

Can you please help?

Thanks

[pbaldy]

Is that really what you want? That would produce either a net price or a percentage, which seems odd. I'd expect something like this:

Total Price: [Selling Price] - [Discount] + [VAT Amount]

If any of the components might be Null, you could use the Nz() function.

[LindaM]

I've tried so many different calculations using IIF statements and Nz function I think I've lost the plot!

Using the Nz function I've put the following in:

Total Price: ((Nz([VAT Amount],0))/([Selling Price]-[Discount])

The figures I'm getting are:

Selling Price VAT Amount Discount Total Price
2.00 0 0 2.00
10.00 0 0.50 -0.10
25.00 0 1.50 -1.34

I haven't a clue where the last 2 Total Price figures are coming from.

The information I have in this Query comes from 3 different tables but all the above fields are from the same table. I've looked through all the affecting tables and there is no other calculations there that should give these figures. I've even looked through all forms and reports and nothing as well.

Basically, I still want the Total Price field to show what the cost is:

Selling Price - Discount + VAT = Total Price (sorry for putting Discount and VAT round wrong way earlier - brain was fried with trying all different calculations! )

Thanks

[pbaldy]

I'm confused as to what the fields contain and what you want to achieve (like why you would be dividing). Can you post a sample db, or at least a few lines of what the data would look like, with various combination's, and what your desired result would be for each?

[LindaM]

Hi there

I've attached jpegs showing the fields in question in the query.

The Total Price calculated field I want should show:

Selling Price - Discount * VAT Amount = Total Price

The VAT Amount field is not set as a percentage, but as a numerical, the other fields are set as currency.

The VAT Amounts would either be 0 or 17.5.

Note, the 2nd record showing £10 in Total Price field should show £9.50 (SP-Discount*VAT).

This is the first time I've tried doing calculated fields for several years and when I first started to create this field I thought I remembered the right calculation...but obviously I didn't remember!! I've tried so many now that I don't even remember the initial calculation, so I've put in a simple calculation in the Total Price field.

Any help would be appreciated.

Thanks

[pbaldy]

I've been out of town; did you get this sorted out? I'm not clear on what the VAT field would normally hold. You're multiplying, so anything multiplied by zero is zero, so that's what's going on with that function.