Showing 0 as last digit of a number past decimal

G

Guest

I need my users to added values that have different number of digits past the
decimal. In particular, last 0 past decimal does not display. Example;

45 displays as 45 should display as 45
45.0 displays as 45 should display as 45.0
45.1 displays as 45.1 should display as 45.1
45.10 displays as 45.1 should display as 45.10
45.01 should display as 45.01 should display as 45.01

In my world, 45.1 and 45.10 are different numbers so need to keep the 0 if
it is entered at the end. Only way I see out of this is to use a text field,
but that opens me up for a whole lot of other problems.
 
G

Guest

Did you try the format() fonction?

Format(yourNumber, "#.00")

If you replace "yourNumber" by 1.1 it will display 1.10

Hope this will help you!

Yanick
 
J

Jeff Boyce

Phoenix

I'm not in your world, so I'm having trouble understanding how 45.1 and
45.10 are different numbers. If I add/subtract/multiple/divide by either of
these, I get the same answer.

Are you saying the DISPLAY/format of these are different? If so, as Yanick
points out, you can use formatting to display your value in whatever form
you wish.

By the way, why are you not looking at 45.0 as you are 45.1, and looking for
45.00 as the form you wish to see?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

Jeff,
Thanks for your reply. My data is scientific data, so it has different
significant figures for different work, depending on the precision of the
test method used to determine the number. So, 45.1 means that the real
result is somewhere between 44.9 and 45.3 (assuming precision is plus or
minus 0.2). For 45.10, I am confident that the number is 45.1 with variation
around the second place past the decimal. This is important because
scientist like me are crazy, no really because it keeps us from reporting
things that are not exactly well know.

Hope this helps
 
J

Jeff Boyce

Thanks, Tom. I suspected we were discussion precision, and Phoenix
confirmed that.

I'll stick with my first impression -- the issue is not what number is
stored. The issue is the number of decimal places displayed.

I suspect it will take some creative code to come up with a
formatting/display that adjusts to the number of decimal places -- the
Format() function in Access that I'm familiar with is fairly "stupid". It
can handle always displaying 1 place, or two places, or ..., but can only do
one of these.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

I wonder why it would not be possible for the Access Development Team at
Microsoft to modify JET so that we could have numeric fields that could be
used to store numbers with significant digits, exactly as entered. This issue
seems to come up frequently enough. Yeah, I know that any future development
of JET is likely a dead issue, other than security enhancements. I think
that's too bad.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks, Tom. I suspected we were discussion precision, and Phoenix
confirmed that.

I'll stick with my first impression -- the issue is not what number is
stored. The issue is the number of decimal places displayed.

I suspect it will take some creative code to come up with a
formatting/display that adjusts to the number of decimal places -- the
Format() function in Access that I'm familiar with is fairly "stupid". It
can handle always displaying 1 place, or two places, or ..., but can only do
one of these.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Douglas J. Steele

Tom Wickerath said:
I wonder why it would not be possible for the Access Development Team at
Microsoft to modify JET so that we could have numeric fields that could be
used to store numbers with significant digits, exactly as entered. This
issue
seems to come up frequently enough. Yeah, I know that any future
development
of JET is likely a dead issue, other than security enhancements. I think
that's too bad.
Click to expand...

Future development of JET isn't a dead issue. There's actually a new version
that'll be coming with Access 12.

Check Erik Rucker's blog entry at
http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx
 
D

Douglas J. Steele

I can't comment, but I would expect more than just the Sharepoint
integration changes Erik mentioned in that entry.
 
G

Guest

Jeff and others, thanks for confirming that Access does not have an easy way
to deal with this. Here is how I will deal with it.

I will convert the field to a text field, and whenever I have to use it to
do calculations I will convert it to a number before I do the calculations.

Another thought just came to me that while the number is being entered as
text, a second, invisible field is populated by the number and that number is
used for calculations. This keeps me from having to do the conversion for
all new querries and such. It has the added advantage of confusing the
innocent person who picks up my database later and tries to change something.


Thanks again for the help.
 
J

James A. Fortune

Phoenix said:
Jeff and others, thanks for confirming that Access does not have an easy way
to deal with this. Here is how I will deal with it.

I will convert the field to a text field, and whenever I have to use it to
do calculations I will convert it to a number before I do the calculations.

Another thought just came to me that while the number is being entered as
text, a second, invisible field is populated by the number and that number is
used for calculations. This keeps me from having to do the conversion for
all new querries and such. It has the added advantage of confusing the
innocent person who picks up my database later and tries to change something.


Thanks again for the help.

:
Click to expand...

Phoenix,

I began something along these lines in:

http://groups.google.com/group/microsoft.public.access/msg/cc1e90fca04bb776

It's possible that comparing the number of digits returned to the number
of places desired can identify the case where Access drops the 0's after
the decimal point. Also, a string is the only way to get them to stay.

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

C# Excel Interop and Decimal Places 2
Percentage Data Type 4
Decimal displays 1, 2, or None 2
Using number with a decimal as a key number? 5
Show last four digits of SS# 3
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
Formatting long decimal numbers as TEXT cell, not refreshing? 2
Calculated control showing decimal places 1

Top