Performing calculations from a drop down list

[snowdrop]

I'm having an issue with a query performing calculations and I hope someone can help me as I'm tearing my hair out!!!



Basically I have a cost form that has 3 different VAT rates in a drop down list. On the form it calculates the value + VAT no problem but when I run a query with the total amount it comes back blank.

Is there something I should be adding into the query? I've tried building an expression in the criteria to calculate all the subtotals but that doesn't work either.

I need to sort this asap as about to go off on leave for a while and no one else knows how to work with the database. I need the reports to be accurate before going.

Any help is gratefully appreciated.

[alansidman]

Post your SQL statement for your query.

[snowdrop]

Is this what you are asking for?


SELECT [Grant Info].[Date Approved], [Grant Info].ID, [Grant Info].Name, [Grant Info].Department, [Grant Info].[Special Accounts ID], [Grant Info].[Subject of Application], [Grant Info].[Grant approved], [Grant Info].[Total Requested], [Grant Info].[Amount approved], [Grant Info].[Funding Source], [Grant Info].[Date Grant Released], [Grant Info].Date
FROM [Grant Info]
WHERE ((([Grant Info].[Grant approved])=Yes) AND (([Grant Info].[Total Requested])=[Grant Info]![Subtotal 1]+[Grant Info]![Subtotal 2]+[Grant Info]![Subtotal 3]+[Grant Info]![Subtotal 4]+[Grant Info]![Subtotal 5]+[Grant Info]![Subtotal 6]+[Grant Info]![Subtotal 7]) AND (([Grant Info].Date) Between [Enter start date] And [Enter end date]));

[alansidman]

Firstly: Using the terms Date and Name should be avoided as they are reserved words in Access and may actually cause Access to misbehave at some point. Suggest you change them, ie. gDate and gName or some other variation.

To try and keep your query clean, I would actually do the calculation of summing the subtotal fields in a new field as an expression:

Perhaps Subtotals: [Grant Info].[Subtotal1] + [Grant Info].Subtotal2]......

and then do an IIf statement to compare the results with the [Total Requested].

[snowdrop]

I tried doing an expression like this already but it still didn't work. I also tried an expression calculating the cost+Vat but it that didn't work either.

Any other suggestions?

[snowdrop]

I have now resolved this issue but thanks for your help.