format a number field to only show 2 decimal places if there are a

[tstew]

This should be simple, but I must be missing something. I have a Number
field, field size is "Single", Format is "#,###", Decimal Places is "Auto"
(no quotes in value boxes). I would have thought Auto would handle it, but it
doesn't.

The filed will have only positive numbers, most will not have decimals, but
some will. I would like to ONLY display decimals if there are any. If I
change the formating to "#,###.##", then I get a period with no decimal
places on whole numbers. I've also played around with the Currency field type
with the same results.

I get:
1,234.
456.34
12,345.67
102,789.

I would like to get:
1,234
456.34
12,345.67
102,789

Any ideas?

Thanks,
Mark

 

[Frank H]

Have you tried just setting the format to General?

 

[tstew]

Thanks, yes I've tried that. It's a picky kind of thing, but that would return:

"123456" instead of "123,456"

or "123456.78" instead of "123,456.78"

My problem (and, it's really more of a learning/puzzle) is that I have an
assistant read a large amount of data to me over the phone and I'm just
trying to make it easy for both of use... and learn a little about format
protacol.

Thanks,
Mark

 

[ahmed mursal]

This should be simple, but I must be missing something. I have a Number
field, field size is "Single", Format is "#,###", Decimal Places is "Auto"
(no quotes in value boxes). I would have thought Auto would handle it, but
it
doesn't.

The filed will have only positive numbers, most will not have decimals,
but
some will. I would like to ONLY display decimals if there are any. If I
change the formating to "#,###.##", then I get a period with no decimal
places on whole numbers. I've also played around with the Currency field
type
with the same results.

I get:
1,234.
456.34
12,345.67
102,789.

I would like to get:
1,234
456.34
12,345.67
102,789

Any ideas?

Thanks,
Mark

 

[Frank H]

I'm assuming you've read the Help sheet on the Format property for numeric
data types, and have seen that the format characters you can use for custom
formats do not accomodate conditional display of decimal places.
So it would seem that rather than jumping through hoops, it would be simpler
to reach an agreement with your assistant on how you want them read to you.

If you want to jump through hoops, you could create a query, or text box on
a form to create a conditional way to display the desired formatting.
You could determine whether a number is a decimal via an expression like:
=IIF([FieldName]-int([FieldName]<>0,Format([FieldName,"format you want for
decimals"),Format([FieldName],"format you want for integers)).
Or, you could write a VBA function to do the same thing.

 

[Ken Snell]

Don't use Format property for this special case; leave it empty. Instead,
use the Control Source property with an expression similar to this:

=IIf([NameOfField] -
Int([NameOfField])=0,Format([NameOfField],"#,###",Format([NameOfField],"#,###.##"))

 

[Tony Toews [MVP]]

My problem (and, it's really more of a learning/puzzle) is that I have an
assistant read a large amount of data to me over the phone and I'm just
trying to make it easy for both of use...

Are there alternatives to reading large amount of data over the phone?
Could you use a Palm or Pocket PC or whatever programmable device
there is available these days. Thus the asst would record the data
into a small simple database which resides on the Palm or Pocket PC
and synch it to the PC. Maybe there's something on Apples iPhone?

Even Excel might work. There is some Palm software which comes with
later models of Palms that allows you to view Word and Excel files in
the Palm and update them. then when you get back to PC you sync them
and the files on the PC are now updated.

Now the trouble is that Palm no longer makes Palms. Instead they are
also phone devices. So I'm kinda keeping me eye on Ebay to purchase a
Palm TX or two or three. Three of them, along with some spare
batteries should last me my life time. (The Palm Tx has both
Bluetooth and WiFi.)

Tony