Why query result not as currency 2

[Moss100]

Hello I have a query called qryMAIN. In this query I have the following calculated field:

Trans_OUT_Calc_VAT_Amount: Format(([Trans_Out_Gross_Amt]/(100+[Trans_VAT_Rate]))*([Trans_VAT_Rate]),"Currency")

This seesm to work OK and presents the field data in a currency format. i.e. if I introduce the criteria >0 its fine.

If I then go to use qryMain as the underlying source to create a further query, if I use this field [Trans_OUT_Calc_VAT_Amount] then it seems to present it as a text field, not currency. For exapmple if I introduce the above criteria is repsresents it as >"0"

Why is the currecny format not maintained - is there a way round this?

Thanks Mark

 

[dhookom]

The Format() function converts your number to text. Why are you setting the format? The format is typically at the point of user interface which should be a form or report.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

The reason I am using format is to prevent the results from having long decimal places. (i.e. 10.23654578 etc)

I'm trying to calculate VAT tax amaounts, so I need either it to be fixed or as a currency.

Is there a better approach?

Basically I have the Gross Cost and The Tax Rate and need to calculate the net VAT amount: i.e.

Gross amount £200, Tax Rate 20%

Calculation: £200/100+20*20 = £33.33

Thanks Mark

 

[strongm]

>The reason I am using format

As Duane says, you only really need to do this for display purposes, not in the underlying query. If you want to see it a currency when viewing the query in table view, just set the fields Format property in the query designer.

 

[dhookom]

There are other functions for rounding that maintain the numeric data type.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

Thank you for this -- I have used the Round function which seems to give the right results.

Many thanks - PS - Is the Round function appropriate or are there better ones?

Mark