CODEWITHSUNDEEP
ms-accessms-access-2007
Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11988

Access adds extra decimals when grouping in query

Did not found anything to solve this.

I'm doing some easy things on an Access Database 2007 32 bit.

Got this table:

Id_iva  Desde       Hasta       Valor_Iva
2       01/01/2000  31/08/2012  18,00%
4       01/09/2012  31/12/2021  21,00%
5       01/01/2022  31/12/2099  25,00%

Valor_iva is a numeric field, single type. I manually input those numbers, with 2 decimals only (in this case all of them are 0, but it could be something like 18,50% or 20,23% and so on)

If I make a query like this:

SELECT T_IVA.Hasta, T_IVA.Valor_Iva FROM T_IVA;

It works as expected and it returns exactly the values:

enter image description here

But If my query is this:

SELECT T_IVA.Hasta, Sum(T_IVA.Valor_Iva) AS SumaDeValor_Iva FROM T_IVA GROUP BY T_IVA.Hasta;

I get extradecimals in some values.

enter image description here

Can't understand where those decimals come from.

I've googled about CAST and TRUNCATE but I could not apply those (or I don't know how to).

WHAT I WANT: I just want to make a GROUP BY query that does not add those decimals.

Thanks in advance.

Upvotes: 0

Views: 233

Answers (1)

Erik A
Erik A

Reputation: 32682

If you want exact results, then cast to an exact type before doing any operations. Or, even better, use an exact (non-floating point) type in the first place.

It seems your values fit in the Currency data type. The Decimal data type can be used for larger values with decimals.

SELECT T_IVA.Hasta, Sum(CCur(T_IVA.Valor_Iva)) AS SumaDeValor_Iva FROM T_IVA GROUP BY T_IVA.Hasta;

Upvotes: 1

Related Questions