How to Perform 2 Calculations in a query and only show result of One

[deepakg27]

Hi guys,

As per the suggestions and guidance provided, I am trying to shift from calculated fields all together and use queries for calculating values and later storing the results to the fields, for my billing database.

I have created a query for Tax Amount, where I am Calculating Amount(AMT = (Qty*Rate)) and further calculating Tax Amount (TAMT =(AMT* TaxRate/100)). Now the query works fine, if I set the property of AMT to show. As soon as I shift the property of AMT to not Show, It starts asking me for value of AMT. I only want to show the value of Tax Amount (i.e. TAMT). As i need that value to store in Tax amount column in my table.



Both calculations are enclosed in ROUND Function.

Your help is much appreciated.

Thanks and Regards
Deepak Gupta

[pbaldy]

If you're not going to show it, why bother with it? Just put that calculation in place of AMT in the final calculation.

[deepakg27]

Thanks Pbaldy,

Never thought of it. Will try that. In any case if I want to still calculate AMT & Tax Rate Separately is it possible to make two calculations and show only one.

Thanks and Regards
Deepak Gupta

[deepakg27]

Unable to make the calculation if I put everything at one place. Syntax problem, unable to write the formula for this part in the query. Please help.

[Micron]

As soon as I shift the property of AMT to not Show, It starts asking me for value of AMT

I'm presuming AMT is a field. If you don't elect to show it, you're not returning it thus you can't use it. You can elect to not show fields by unchecking the show box and still use the field to apply criteria to the results. But if you expect to use a field in a calculation, you have to return it. If you are following other advice, you were likely told to use forms along with your queries, in which case, you return whatever values are needed for the calculations and either perform the calculations on forms in unbound textboxes, or if in queries, you return the value to the form based on that query but don't show it on the form. Not sure you should be storing the calculations either.

[pbaldy]

This doesn't work?

((Qty*Rate) * TaxRate/100)

[deepakg27]

Thanks pbaldy.
Found my mistake.

Regards
Deepak Gupta

[pbaldy]

Glad you found it.