Percent Sign on Report

[JD McLeod]

I have a table called “Loans†in my database. One of the fields in the table
is [Rate] for interest rate. On certain reports, I need the rate to appear
in the following format 6.00%. I always need it to have 2 decimal places.
The field type in my table is text since that is how most of my client’s have
given me the data. I used the following formula on some reports and got the
result I wanted, but I am not sure if this is the best way to do it.
[Rate]*1/100 and then I selected the Percent as the format for the text
box. However, when I apply this same formula against another table for lines
of credit, I get #Error. How is the best way to store the information in the
table, as number or text. And how can I get the desired result on my reports.
Thanks.

 

[Allen Browne]

If you really must store it as Text, use Val() to get the to get the numeric
value of the text.

Val() doesn't cope with nulls, so you will need to use Nz() inside Val(),
e.g.:
=Val(Nz([YourTextFieldNameHere], "0"))

You will then be able to set the Format property of the text box on the
report to Percent. It will default to 2 places, but set the text box's
Decimal Places if you want something else.

Make sure the Name property of this text box is not the same as the name of
the field.

I'm still not convinced there is any justification for storing this as a
Text field, other than in a temporary table you use to massage it on the way
into the real table.

 

[JD McLeod]

Thanks Allen,
That helped and i also found that i did have the name of the text box
control the same as the name of the field. When i changed that, my original
formula worked. I am still learning Access, so hopefully i will figure out a
better way to store this data. I was thinking that if i could get the data
from the client in Excel then i could convert it to a number before i import
it into Access. Any thoughts you have are much appreciated. thanks again.

If you really must store it as Text, use Val() to get the to get the numeric
value of the text.

Val() doesn't cope with nulls, so you will need to use Nz() inside Val(),
e.g.:
=Val(Nz([YourTextFieldNameHere], "0"))

You will then be able to set the Format property of the text box on the
report to Percent. It will default to 2 places, but set the text box's
Decimal Places if you want something else.

Make sure the Name property of this text box is not the same as the name of
the field.

I'm still not convinced there is any justification for storing this as a
Text field, other than in a temporary table you use to massage it on the way
into the real table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table called “Loans†in my database. One of the fields in the
table
is [Rate] for interest rate. On certain reports, I need the rate to
appear
in the following format 6.00%. I always need it to have 2 decimal
places.
The field type in my table is text since that is how most of my client’s
have
given me the data. I used the following formula on some reports and got
the
result I wanted, but I am not sure if this is the best way to do it.
[Rate]*1/100 and then I selected the Percent as the format for the
text
box. However, when I apply this same formula against another table for
lines
of credit, I get #Error. How is the best way to store the information in
the
table, as number or text. And how can I get the desired result on my
reports.
Thanks.

 

[Allen Browne]

Good: you've identified an important issue: Access gets confused it a
control has the same name as a field, but it is bound to something else.

Access is more likely to understand the data as numeric from Excel, but only
if it is consistent. If the column contains non-numeric values like "N/A",
Access will still treat is as text.

As a guide, the data types are important in a database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,
That helped and i also found that i did have the name of the text box
control the same as the name of the field. When i changed that, my
original
formula worked. I am still learning Access, so hopefully i will figure
out a
better way to store this data. I was thinking that if i could get the
data
from the client in Excel then i could convert it to a number before i
import
it into Access. Any thoughts you have are much appreciated. thanks
again.

If you really must store it as Text, use Val() to get the to get the
numeric
value of the text.

Val() doesn't cope with nulls, so you will need to use Nz() inside Val(),
e.g.:
=Val(Nz([YourTextFieldNameHere], "0"))

You will then be able to set the Format property of the text box on the
report to Percent. It will default to 2 places, but set the text box's
Decimal Places if you want something else.

Make sure the Name property of this text box is not the same as the name
of
the field.

I'm still not convinced there is any justification for storing this as a
Text field, other than in a temporary table you use to massage it on the
way
into the real table.

I have a table called “Loans†in my database. One of the fields in the
table
is [Rate] for interest rate. On certain reports, I need the rate to
appear
in the following format 6.00%. I always need it to have 2 decimal
places.
The field type in my table is text since that is how most of my client’s
have
given me the data. I used the following formula on some reports and
got
the
result I wanted, but I am not sure if this is the best way to do it.
[Rate]*1/100 and then I selected the Percent as the format for the
text
box. However, when I apply this same formula against another table for
lines
of credit, I get #Error. How is the best way to store the information
in
the
table, as number or text. And how can I get the desired result on my
reports.