Rounding Decimals

  • Thread starter Thread starter rkelly31
  • Start date Start date
R

rkelly31

I have a report that gives me a percent in one column. I want that column to
round up if the percent is less than 1. Right now it's just showing as 0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places I get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
 
In the query serving as the report's RecordSource, replace the "Last Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010, Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a number
value, not a percentage value.
 
Thanks for your response, but I'm getting #ERROR for some reason.
--
L.Kelly


Ken Snell MVP said:
In the query serving as the report's RecordSource, replace the "Last Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010, Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
I have a report that gives me a percent in one column. I want that column
to
round up if the percent is less than 1. Right now it's just showing as 0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places I get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
 
Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some reason. Does
that matter?

--
L.Kelly


Ken Snell MVP said:
In the query serving as the report's RecordSource, replace the "Last Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010, Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
I have a report that gives me a percent in one column. I want that column
to
round up if the percent is less than 1. Right now it's just showing as 0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places I get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
 
Which version of ACCESS are you using? In my test in ACCESS 2003, I don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



rkelly31 said:
Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some reason. Does
that matter?

--
L.Kelly


Ken Snell MVP said:
In the query serving as the report's RecordSource, replace the "Last Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010, Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
I have a report that gives me a percent in one column. I want that
column
to
round up if the percent is less than 1. Right now it's just showing as
0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places I get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
2007
--
L.Kelly


Ken Snell MVP said:
Which version of ACCESS are you using? In my test in ACCESS 2003, I don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



rkelly31 said:
Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some reason. Does
that matter?

--
L.Kelly


Ken Snell MVP said:
In the query serving as the report's RecordSource, replace the "Last Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010, Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a report that gives me a percent in one column. I want that
column
to
round up if the percent is less than 1. Right now it's just showing as
0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places I get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
I've tested the expression in ACCESS 2007. Yes, ACCESS changes "0.00%" to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





rkelly31 said:
2007
--
L.Kelly


Ken Snell MVP said:
Which version of ACCESS are you using? In my test in ACCESS 2003, I don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



rkelly31 said:
Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace the "Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a report that gives me a percent in one column. I want that
column
to
round up if the percent is less than 1. Right now it's just showing
as
0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
Excuse me if this is a dumb question, but how do I find out?
--
L.Kelly


Ken Snell MVP said:
I've tested the expression in ACCESS 2007. Yes, ACCESS changes "0.00%" to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





rkelly31 said:
2007
--
L.Kelly


Ken Snell MVP said:
Which version of ACCESS are you using? In my test in ACCESS 2003, I don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace the "Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a report that gives me a percent in one column. I want that
column
to
round up if the percent is less than 1. Right now it's just showing
as
0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
Assuming that the field is in a table, open that table in design view, click
on that field in the list at top, and read the datatype right next to the
field name. Then look at Field Size at bottom of window. Need to know both
the Data Type and Field Size info.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
Excuse me if this is a dumb question, but how do I find out?
--
L.Kelly


Ken Snell MVP said:
I've tested the expression in ACCESS 2007. Yes, ACCESS changes "0.00%" to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





rkelly31 said:
2007
--
L.Kelly


:

Which version of ACCESS are you using? In my test in ACCESS 2003, I
don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace the
"Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a
number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a report that gives me a percent in one column. I want
that
column
to
round up if the percent is less than 1. Right now it's just
showing
as
0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places
I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
It's a text field. I'm not sure why though because I didn't build this
database, I'm trying to work with someone else's disaster.
--
L.Kelly


Ken Snell MVP said:
Assuming that the field is in a table, open that table in design view, click
on that field in the list at top, and read the datatype right next to the
field name. Then look at Field Size at bottom of window. Need to know both
the Data Type and Field Size info.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
Excuse me if this is a dumb question, but how do I find out?
--
L.Kelly


Ken Snell MVP said:
I've tested the expression in ACCESS 2007. Yes, ACCESS changes "0.00%" to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





2007
--
L.Kelly


:

Which version of ACCESS are you using? In my test in ACCESS 2003, I
don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace the
"Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a
number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a report that gives me a percent in one column. I want
that
column
to
round up if the percent is less than 1. Right now it's just
showing
as
0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal places
I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
Text field, eh? OK, let's try this expression:

New LYTD %: IIf(CDbl([LYTD %])>=0.01,Format(CDbl([LYTD
%]),"0%",Format(CDbl([LYTD %]),"Percent")))

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




rkelly31 said:
It's a text field. I'm not sure why though because I didn't build this
database, I'm trying to work with someone else's disaster.
--
L.Kelly


Ken Snell MVP said:
Assuming that the field is in a table, open that table in design view,
click
on that field in the list at top, and read the datatype right next to the
field name. Then look at Field Size at bottom of window. Need to know
both
the Data Type and Field Size info.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
Excuse me if this is a dumb question, but how do I find out?
--
L.Kelly


:

I've tested the expression in ACCESS 2007. Yes, ACCESS changes "0.00%"
to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





2007
--
L.Kelly


:

Which version of ACCESS are you using? In my test in ACCESS 2003, I
don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some
reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace the
"Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a
number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a report that gives me a percent in one column. I want
that
column
to
round up if the percent is less than 1. Right now it's just
showing
as
0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal
places
I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
It's still giving me an error. One thing I think I should've added is that
LYTD % is not inputed anywhere it's calculated in another query like this:

LYTD %: Count([urs number])/Max([Total])

urs number is the text field
--
L.Kelly


Ken Snell MVP said:
Text field, eh? OK, let's try this expression:

New LYTD %: IIf(CDbl([LYTD %])>=0.01,Format(CDbl([LYTD
%]),"0%",Format(CDbl([LYTD %]),"Percent")))

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




rkelly31 said:
It's a text field. I'm not sure why though because I didn't build this
database, I'm trying to work with someone else's disaster.
--
L.Kelly


Ken Snell MVP said:
Assuming that the field is in a table, open that table in design view,
click
on that field in the list at top, and read the datatype right next to the
field name. Then look at Field Size at bottom of window. Need to know
both
the Data Type and Field Size info.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Excuse me if this is a dumb question, but how do I find out?
--
L.Kelly


:

I've tested the expression in ACCESS 2007. Yes, ACCESS changes "0.00%"
to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





2007
--
L.Kelly


:

Which version of ACCESS are you using? In my test in ACCESS 2003, I
don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD %],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some
reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace the
"Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage], "0.00%")


The above assumes that the value in [Last Year Percentage] is a
number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a report that gives me a percent in one column. I want
that
column
to
round up if the percent is less than 1. Right now it's just
showing
as
0,
but if I change it then the whole numbers show as a decimal.

For Example:

Last Year Percentage If I Change to 2 decimal
places
I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
Let's see all the specific information about what you're using/doing. Is it
possible that Max([Total]) can be zero? If yes, that will give a "dividing
by zero" error.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
It's still giving me an error. One thing I think I should've added is
that
LYTD % is not inputed anywhere it's calculated in another query like this:

LYTD %: Count([urs number])/Max([Total])

urs number is the text field
--
L.Kelly


Ken Snell MVP said:
Text field, eh? OK, let's try this expression:

New LYTD %: IIf(CDbl([LYTD %])>=0.01,Format(CDbl([LYTD
%]),"0%",Format(CDbl([LYTD %]),"Percent")))

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




rkelly31 said:
It's a text field. I'm not sure why though because I didn't build this
database, I'm trying to work with someone else's disaster.
--
L.Kelly


:

Assuming that the field is in a table, open that table in design view,
click
on that field in the list at top, and read the datatype right next to
the
field name. Then look at Field Size at bottom of window. Need to know
both
the Data Type and Field Size info.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Excuse me if this is a dumb question, but how do I find out?
--
L.Kelly


:

I've tested the expression in ACCESS 2007. Yes, ACCESS changes
"0.00%"
to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





2007
--
L.Kelly


:

Which version of ACCESS are you using? In my test in ACCESS
2003, I
don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD
%],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some
reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace
the
"Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage],
"0.00%")


The above assumes that the value in [Last Year Percentage] is
a
number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I have a report that gives me a percent in one column. I
want
that
column
to
round up if the percent is less than 1. Right now it's
just
showing
as
0,
but if I change it then the whole numbers show as a
decimal.

For Example:

Last Year Percentage If I Change to 2 decimal
places
I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
Yes it's possible
--
L.Kelly


Ken Snell MVP said:
Let's see all the specific information about what you're using/doing. Is it
possible that Max([Total]) can be zero? If yes, that will give a "dividing
by zero" error.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
It's still giving me an error. One thing I think I should've added is
that
LYTD % is not inputed anywhere it's calculated in another query like this:

LYTD %: Count([urs number])/Max([Total])

urs number is the text field
--
L.Kelly


Ken Snell MVP said:
Text field, eh? OK, let's try this expression:

New LYTD %: IIf(CDbl([LYTD %])>=0.01,Format(CDbl([LYTD
%]),"0%",Format(CDbl([LYTD %]),"Percent")))

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




It's a text field. I'm not sure why though because I didn't build this
database, I'm trying to work with someone else's disaster.
--
L.Kelly


:

Assuming that the field is in a table, open that table in design view,
click
on that field in the list at top, and read the datatype right next to
the
field name. Then look at Field Size at bottom of window. Need to know
both
the Data Type and Field Size info.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Excuse me if this is a dumb question, but how do I find out?
--
L.Kelly


:

I've tested the expression in ACCESS 2007. Yes, ACCESS changes
"0.00%"
to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





2007
--
L.Kelly


:

Which version of ACCESS are you using? In my test in ACCESS
2003, I
don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD
%],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for some
reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace
the
"Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >= 0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage],
"0.00%")


The above assumes that the value in [Last Year Percentage] is
a
number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I have a report that gives me a percent in one column. I
want
that
column
to
round up if the percent is less than 1. Right now it's
just
showing
as
0,
but if I change it then the whole numbers show as a
decimal.

For Example:

Last Year Percentage If I Change to 2 decimal
places
I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
OK. What value do you want to show in the query if the Max([Total]) should
be zero? Assuming that the [LYTD %] value should be zero if the Max([Total])
is zero, change the expression for [LYTD %] to this:

LYTD %: IIf(Max([Total]) = 0, 0, Count([urs number])/Max([Total]))


Then this expression should work:

New LYTD %: IIf(CDbl([LYTD %])>=0.01,Format(CDbl([LYTD
%]),"0%",Format(CDbl([LYTD %]),"Percent")))

--

Ken Snell
http://www.accessmvp.com/KDSnell/



rkelly31 said:
Yes it's possible
--
L.Kelly


Ken Snell MVP said:
Let's see all the specific information about what you're using/doing. Is
it
possible that Max([Total]) can be zero? If yes, that will give a
"dividing
by zero" error.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


rkelly31 said:
It's still giving me an error. One thing I think I should've added is
that
LYTD % is not inputed anywhere it's calculated in another query like
this:

LYTD %: Count([urs number])/Max([Total])

urs number is the text field
--
L.Kelly


:

Text field, eh? OK, let's try this expression:

New LYTD %: IIf(CDbl([LYTD %])>=0.01,Format(CDbl([LYTD
%]),"0%",Format(CDbl([LYTD %]),"Percent")))

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




It's a text field. I'm not sure why though because I didn't build
this
database, I'm trying to work with someone else's disaster.
--
L.Kelly


:

Assuming that the field is in a table, open that table in design
view,
click
on that field in the list at top, and read the datatype right next
to
the
field name. Then look at Field Size at bottom of window. Need to
know
both
the Data Type and Field Size info.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Excuse me if this is a dumb question, but how do I find out?
--
L.Kelly


:

I've tested the expression in ACCESS 2007. Yes, ACCESS changes
"0.00%"
to
"Percent", and that is ok.

The expression works fine for me there.

What datatype is the [LYTD %] field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





2007
--
L.Kelly


:

Which version of ACCESS are you using? In my test in ACCESS
2003, I
don't
see the "0.00%" changing to "Percent".
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Here's the query:

New LYTD %: IIf([LYTD %]>=0.01,Format([LYTD
%],"0%",Format([LYTD
%],"Percent"))) It's changing "0.00%" to "Percent" for
some
reason.
Does
that matter?

--
L.Kelly


:

In the query serving as the report's RecordSource, replace
the
"Last
Year
Percentage" field with this calculated field:

NewLastYearPercentage: IIf([Last Year Percentage] >=
0.010,
Format([Last
Year Percentage], "0%", Format([Last Year Percentage],
"0.00%")


The above assumes that the value in [Last Year Percentage]
is
a
number
value, not a percentage value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I have a report that gives me a percent in one column. I
want
that
column
to
round up if the percent is less than 1. Right now it's
just
showing
as
0,
but if I change it then the whole numbers show as a
decimal.

For Example:

Last Year Percentage If I Change to 2
decimal
places
I
get
1% .95%
2% 1.6 %
5% 5.1%
0% 0.38%
4% 3.9%

How do I get this?

1%
2%
5%
0.38%
3.9%

I hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top