Decimal places

[Guest]

Hi all

I have several amount fields in my system, we deal in different currencies
so I can not use the Currency type. I have the fields set to number, fixed,
2. In my query when I enter a calculated field I get 100.1 instead of 100.10
etc, tried experimenting with round and format in the query but no joy so far
(eg TotalCharge: Sum(Format([ChargeAmount],"Currency"))

Can anyone help with this please?

Thanks
Sue

 

[Brendan Reynolds]

Provided that you do not need any more than four decimal places, the fact
that you use different currencies does not prevent you from using the
Currency data type. Perhaps you have been discouraged from using it by the
fact that, when you create a new field with the Currency data type, Access
automatically applies the Currency format as well. But that is just a
default - you can use the Currency data type and change the format.

Alternatively, if you prefer, you could use the Decimal data type instead.
(In the graphical designer, you choose Number as the data type and Decimal
as the Field Size). But there are 'issues' with the Decimal data type - see
the URL below for details ...

http://allenbrowne.com/bug-08.html

 

[Guest]

Thanks you are correct in your assumption. However, I did change the type to
currency as a test just to see and my calculated field in query still didn't
display fixed two decimal places?

TotalCharge=Sum([ChargeAmount])

Is this because the data had been entered when it wasn't currency?

Thanks
Sue

Provided that you do not need any more than four decimal places, the fact
that you use different currencies does not prevent you from using the
Currency data type. Perhaps you have been discouraged from using it by the
fact that, when you create a new field with the Currency data type, Access
automatically applies the Currency format as well. But that is just a
default - you can use the Currency data type and change the format.

Alternatively, if you prefer, you could use the Decimal data type instead.
(In the graphical designer, you choose Number as the data type and Decimal
as the Field Size). But there are 'issues' with the Decimal data type - see
the URL below for details ...

http://allenbrowne.com/bug-08.html

--
Brendan Reynolds

Hi all

I have several amount fields in my system, we deal in different currencies
so I can not use the Currency type. I have the fields set to number,
fixed,
2. In my query when I enter a calculated field I get 100.1 instead of
100.10
etc, tried experimenting with round and format in the query but no joy so
far
(eg TotalCharge: Sum(Format([ChargeAmount],"Currency"))

Can anyone help with this please?

Thanks
Sue

 

[Brendan Reynolds]

There are a number of potential issues here ...

There could be fractional values that are left over as a result of rounding
errors prior to converting the data type, yes. On the other hand, the
Currency data type itself, while not subject to floating-point rounding
errors, does permit up to four decimal places, and you may get numbers that
use more than two decimal places as a result of certain calculations. For
example, here in Ireland we have a 13.5 per cent VAT rate, which gives rise
to results like 10.90 * .135 = 1.4715. If you have calculations like that,
you need to decide how you want to handle the results - do you round the
result of individual calculations, or round only totals, and whether to use
'banker's rounding'.

Access (2000 and later) has a build-in Round function that does use
'banker's rounding', or there is code to implement a custom rounding
function at the following URL ...
http://www.mvps.org/access/modules/mdl0054.htm

Also, Access tends to 'guess' at the data type of calculated results - see
what Allen Browne has on this subject at the following URL ...
http://allenbrowne.com/ser-45.html

And finally, if the query will be used as the recordsource of a form or
report, you may find it both easier and more efficient to perform formatting
in the form or report rather than in the query.

--
Brendan Reynolds

Thanks you are correct in your assumption. However, I did change the type
to
currency as a test just to see and my calculated field in query still
didn't
display fixed two decimal places?

TotalCharge=Sum([ChargeAmount])

Is this because the data had been entered when it wasn't currency?

Thanks
Sue

Provided that you do not need any more than four decimal places, the fact
that you use different currencies does not prevent you from using the
Currency data type. Perhaps you have been discouraged from using it by
the
fact that, when you create a new field with the Currency data type,
Access
automatically applies the Currency format as well. But that is just a
default - you can use the Currency data type and change the format.

Alternatively, if you prefer, you could use the Decimal data type
instead.
(In the graphical designer, you choose Number as the data type and
Decimal
as the Field Size). But there are 'issues' with the Decimal data type -
see
the URL below for details ...

http://allenbrowne.com/bug-08.html

--
Brendan Reynolds

Hi all

I have several amount fields in my system, we deal in different
currencies
so I can not use the Currency type. I have the fields set to number,
fixed,
2. In my query when I enter a calculated field I get 100.1 instead of
100.10
etc, tried experimenting with round and format in the query but no joy
so
far
(eg TotalCharge: Sum(Format([ChargeAmount],"Currency"))

Can anyone help with this please?

Thanks
Sue

 

[Guest]

Hey Sue, you can format the number like this:

TotalCharge: FormatNumber(Sum([ChargeAmount]),2)

This will display the number with two decimal places.

 

[Guest]

Thanks Brendan for all this useful information!

The user initially enters the amount which is to be charged to the customer
in euros, gbp, lvl or whatever currency he spent but then later on in a
reconciliation function the sterling amount is calculated by an exchange rate
which will have more than 4 decimals.

At the moment I don't do any rounding. I've not done the business analysis
with my colleague on the VAT side of the system yet so I am sure things are
only going to get worse! The VAT on mileage is apparently calculated via a
very complicated lookup table based on cost of fuel and value of car used
etc, they do it manually at the mo. Never was very good at accounting ;-)

I will eventually want to base a report on this query so I will try that now
and see what I get in the report.

Thanks
Sue

There are a number of potential issues here ...

There could be fractional values that are left over as a result of rounding
errors prior to converting the data type, yes. On the other hand, the
Currency data type itself, while not subject to floating-point rounding
errors, does permit up to four decimal places, and you may get numbers that
use more than two decimal places as a result of certain calculations. For
example, here in Ireland we have a 13.5 per cent VAT rate, which gives rise
to results like 10.90 * .135 = 1.4715. If you have calculations like that,
you need to decide how you want to handle the results - do you round the
result of individual calculations, or round only totals, and whether to use
'banker's rounding'.

Access (2000 and later) has a build-in Round function that does use
'banker's rounding', or there is code to implement a custom rounding
function at the following URL ...
http://www.mvps.org/access/modules/mdl0054.htm

Also, Access tends to 'guess' at the data type of calculated results - see
what Allen Browne has on this subject at the following URL ...
http://allenbrowne.com/ser-45.html

And finally, if the query will be used as the recordsource of a form or
report, you may find it both easier and more efficient to perform formatting
in the form or report rather than in the query.

--
Brendan Reynolds

Thanks you are correct in your assumption. However, I did change the type
to
currency as a test just to see and my calculated field in query still
didn't
display fixed two decimal places?

TotalCharge=Sum([ChargeAmount])

Is this because the data had been entered when it wasn't currency?

Thanks
Sue

Provided that you do not need any more than four decimal places, the fact
that you use different currencies does not prevent you from using the
Currency data type. Perhaps you have been discouraged from using it by
the
fact that, when you create a new field with the Currency data type,
Access
automatically applies the Currency format as well. But that is just a
default - you can use the Currency data type and change the format.

Alternatively, if you prefer, you could use the Decimal data type
instead.
(In the graphical designer, you choose Number as the data type and
Decimal
as the Field Size). But there are 'issues' with the Decimal data type -
see
the URL below for details ...

http://allenbrowne.com/bug-08.html

--
Brendan Reynolds


Hi all

I have several amount fields in my system, we deal in different
currencies
so I can not use the Currency type. I have the fields set to number,
fixed,
2. In my query when I enter a calculated field I get 100.1 instead of
100.10
etc, tried experimenting with round and format in the query but no joy
so
far
(eg TotalCharge: Sum(Format([ChargeAmount],"Currency"))

Can anyone help with this please?

Thanks
Sue

 

[Guest]

Thanks, this has no effect either. I've done the formatting in the report
instead now though and this works fine.

Sue

Hey Sue, you can format the number like this:

TotalCharge: FormatNumber(Sum([ChargeAmount]),2)

This will display the number with two decimal places.

Hi all

I have several amount fields in my system, we deal in different currencies
so I can not use the Currency type. I have the fields set to number, fixed,
2. In my query when I enter a calculated field I get 100.1 instead of 100.10
etc, tried experimenting with round and format in the query but no joy so far
(eg TotalCharge: Sum(Format([ChargeAmount],"Currency"))

Can anyone help with this please?

Thanks
Sue

 

[GAVO-UK]

Try This

TotalCharge: Sum(Format([ChargeAmount],"0.00"))

GAVO

 

[Guest]

Thanks for the suggestion but I've already tried this and it still didn't
work. Access 2003 converts this to "fixed" when you enter 0.00.

 

[Guest]

Sue --

Interesting...I tested it and it worked on my calculations but if you were
able to format it in the report at least it is working now.

Thanks, this has no effect either. I've done the formatting in the report
instead now though and this works fine.

Sue

Hey Sue, you can format the number like this:

TotalCharge: FormatNumber(Sum([ChargeAmount]),2)

This will display the number with two decimal places.

Hi all

I have several amount fields in my system, we deal in different currencies
so I can not use the Currency type. I have the fields set to number, fixed,
2. In my query when I enter a calculated field I get 100.1 instead of 100.10
etc, tried experimenting with round and format in the query but no joy so far
(eg TotalCharge: Sum(Format([ChargeAmount],"Currency"))

Can anyone help with this please?

Thanks
Sue

 

[Guest]

Hi,
Don't know if you have try the manual method that have always worked for me,
but if you haven't, try this:
On you query and on your currency field:
Right-click > Properties > Format = Currency > Decimal places = 2

See if this help.,.....