Numbers in Standard format in a listbox

  • Thread starter Thread starter Mr. Smith
  • Start date Start date
M

Mr. Smith

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
 
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")
 
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



Wayne Morgan said:
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")

--
Wayne Morgan
MS Access MVP


Mr. Smith said:
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
Click to expand...
Click to expand...
 
?Trim(Format(123456789, "# ### ###"))
123 456 789

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

?Trim(Format(789, "# ### ###"))
789
 
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
 
You can include both a formatted and unformatted field in the underlying
query. Sort on the unformatted field, but display the formatted one.
 
Back
Top