Numbers in Standard format in a listbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mr. Smith - 08 Apr 2005 14:00 GMT

Hi
One of my columns in a list box contains numbers. I want to show them on the
form 526 250 and not 526250,36. Is it possible to get the numbers to look
"proper"?

Thanks Wayne and John for beeing helpfull on my recent post regarding
formating. I hope it's a work around on this one, since it apply to all the
rows in a list box.....

Regards
Mr. Smith

Reply to this Message

Wayne Morgan - 08 Apr 2005 14:33 GMT

You would need to make that field in the Row Source query be a calculated
field. It would look something like:

MyFormattedField: Format([FieldName], "000 000")
or in SQL
Format([FieldName], "000 000") As MyFormattedField

Be aware that the Format function returns a text value. Also, that it may
"round off" the value in the ones place depending on the value in the
decimal portion of the number. If you don't want it to do that, you will
need to truncate the decimal first.

MyFormattedField: Format(Int([FieldName]), "000 000")

Signature

Wayne Morgan
MS Access MVP

> Hi
> One of my columns in a list box contains numbers. I want to show them on
[quoted text clipped - 9 lines]
> Regards
> Mr. Smith

Reply to this Message

Mr. Smith - 08 Apr 2005 14:50 GMT

Thanks Wayne
It works if the numbers are between 100 000 and 999 999. If I get 1 million
(1 000 000) it does not show. If I use the format ("0 000 000") all numbers
< 1 mill shows like 0 800 000.

Tried to use the custom Excel format string # ##0, but it did not work
either.

Any clues?
Thanks again anyway.

Mr. Smith

> You would need to make that field in the Row Source query be a calculated
> field. It would look something like:
[quoted text clipped - 23 lines]
> > Regards
> > Mr. Smith

Reply to this Message

Van T. Dinh - 08 Apr 2005 16:30 GMT

?Trim(Format(123456789, "# ### ###"))
123 456 789

?Trim(Format(456789, "# ### ###"))
456 789

?Trim(Format(789, "# ### ###"))
789

Signature

HTH
Van T. Dinh
MVP (Access)

> Thanks Wayne
> It works if the numbers are between 100 000 and 999 999. If I get 1 million
[quoted text clipped - 8 lines]
>
> Mr. Smith

Reply to this Message

Mr. Smith - 08 Apr 2005 17:02 GMT

Sorry.
Got a little confused since I had the numbers sorted DESC. After doing the
formating the 1 million record was not in the TOP 20 anymore since it was
sorted as TEXT and not NUMBERS.

OK now. Thanks both Van and Wayne

Mr. Smith

> ?Trim(Format(123456789, "# ### ###"))
> 123 456 789
[quoted text clipped - 19 lines]
> >
> > Mr. Smith

Reply to this Message

Douglas J. Steele - 09 Apr 2005 12:52 GMT

You can include both a formatted and unformatted field in the underlying
query. Sort on the unformatted field, but display the formatted one.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Sorry.
> Got a little confused since I had the numbers sorted DESC. After doing the
[quoted text clipped - 28 lines]
>> >
>> > Mr. Smith

Reply to this Message