Decimal Trailing Zero Formating

[duecesup]

Im working with a numeric field that infrequently uses decimals. I know that
Access 2003 does not store trailing zeros in the number field but would like
to show zeros when printing reports.

I would like only to show zeros for non integers so the report would read

1
2
3
4.01
4.02
.....
4.10
5

I do not want the report to read
1.00
2.00
3.00
4.01
4.02
...
4.10
5.00

How can I format this either using code or in the properties of the report
field.

Thanks

 

[Marshall Barton]

Im working with a numeric field that infrequently uses decimals. I know that
Access 2003 does not store trailing zeros in the number field but would like
to show zeros when printing reports.

I would like only to show zeros for non integers so the report would read

1
2
3
4.01
4.02
....
4.10
5

I do not want the report to read
1.00
2.00
3.00
4.01
4.02
..
4.10
5.00

How can I format this either using code or in the properties of the report
field.

The best you can get from setting the Format property is to
use:
0.##
which would produce:
1.
2.
3.
4.01
5.

You coud use a control source expression to get the result
you posted:
=Iff(thefield = Int(thefield), Format(thefield,"0"),
Format(thefield, "0.00")

but you may prefer using:
=Iff(thefield = Int(thefield), Format(thefield,"0"),
Format(thefield, "0.0#")

 

[John Spencer]

You might want to use
IIF and not IFF

One is an immediate If and the other is a test for friendly or not
friendly (Identification Friend or Foe).



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

;-)

Thanks for catching that typo John.

 

[duecesup via AccessMonster.com]

Thank you both for the coding I'll let you know how the test goes.

;-)

Thanks for catching that typo John.

You might want to use
IIF and not IFF

[quoted text clipped - 23 lines]

 

[duecesup via AccessMonster.com]

I have been trying to place the code into the control source of the field in
the report. The field name is Catalog Number. Should I place the field
name in brackets because there is a space
=IIf([CAtalog Number] = Int([CAtalog Number]), Format([CAtalog Number],"0"),
Format([CAtalog Number], "0.0#")

Or should I be building this code in the query.
Thanks

 

[John Spencer]

Yes, you should use brackets since your field name has spaces. You
should also have one more right parentheses at the end of the expression.

=IIf([CAtalog Number] = Int([CAtalog Number]), Format([CAtalog Number],"0"),
Format([CAtalog Number], "0.0#"))

If you are doing this in a control, make sure the control's name is not
the name of any field. In other words, if the control's name is Catalog
Number change it to txtCatalogNumber.

If you wish you can do this in the query. Just leave off the equal sign
at the beginning.

Field: CatalogNumber: =IIf([CAtalog Number] = Int([CAtalog Number]),
Format([CAtalog Number],"0"),
Format([CAtalog Number], "0.0#"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

I have been trying to place the code into the control source of the field in
the report. The field name is Catalog Number. Should I place the field
name in brackets because there is a space
=IIf([CAtalog Number] = Int([CAtalog Number]), Format([CAtalog Number],"0"),
Format([CAtalog Number], "0.0#")

Or should I be building this code in the query.

The brackets are mandatory for names with a space (or other
non alphanumeric characters).

You can probably put it in either place, but since this is
purely a question of formatting, I would lean towards using
the text box expression.

When you say you are "trying", does that mean you are having
a problem?

 

[duecesup via AccessMonster.com]

I am indeed having a problem. I have placed the code in the query and also
placed the code in the control box in the report. both places have yeilded
the same result in which the decimal still does not insert the ending zero.
14.10 still appears as 14.1. Is there a way i can use the table's format
property to create a correct formating whilst leaving whole numbers.
Thanks

I have been trying to place the code into the control source of the field in
the report. The field name is Catalog Number. Should I place the field

[quoted text clipped - 3 lines]

Or should I be building this code in the query.

The brackets are mandatory for names with a space (or other
non alphanumeric characters).

You can probably put it in either place, but since this is
purely a question of formatting, I would lean towards using
the text box expression.

When you say you are "trying", does that mean you are having
a problem?

 

[Marshall Barton]

I am indeed having a problem. I have placed the code in the query and also
placed the code in the control box in the report. both places have yeilded
the same result in which the decimal still does not insert the ending zero.
14.10 still appears as 14.1. Is there a way i can use the table's format
property to create a correct formating whilst leaving whole numbers.

Of you always want two decimal places, then use 0.00 instead
of 0.0#

 

[duecesup via AccessMonster.com]

I need only decimal places for those numbers that are followed by the decimal
point. While the 0.00 works, it places the .00 after every number. The
problem with 0.0# is the # is automatically converted to .00 in the formating
properties. what i need is a code that will delineate between whole and
decimal numbers and will add a zero when the decimal equals .10 .20 .30 etc..

 

[Marshall Barton]

I need only decimal places for those numbers that are followed by the decimal
point. While the 0.00 works, it places the .00 after every number. The
problem with 0.0# is the # is automatically converted to .00 in the formating
properties. what i need is a code that will delineate between whole and
decimal numbers and will add a zero when the decimal equals .10 .20 .30 etc..

It sounds like you lost track of my original response and
John's correction) that said to remove whatever is in the
Format property and use a control source expression like:

=IIf(thefield = Int(thefield), Format(thefield,"0"),
Format(thefield, "0.00")

 

[duecesup via AccessMonster.com]

I just placed that code back into the control While 17.1=17.10 as is what I
would like, whole numbers now contain .00 ie 18=18.00

I see what the code is trying to do but I think something is missing in the
control formula but cannot figure out what.\

Again thanks for your time in working on this

 

[duecesup via AccessMonster.com]

ok im the village idiot, i need spell check

its perfect. Thanks

I just placed that code back into the control While 17.1=17.10 as is what I
would like, whole numbers now contain .00 ie 18=18.00

I see what the code is trying to do but I think something is missing in the
control formula but cannot figure out what.\

Again thanks for your time in working on this
[quoted text clipped - 8 lines]

=IIf(thefield = Int(thefield), Format(thefield,"0"),
Format(thefield, "0.00")