Format listbox column for currency

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

Nick Mirro

Hello. The row source for a listbox is a query. One of the fields is a
dollar amount I would like to show as formatted for currency. Do I add code
to the query field?

If so, may I ask what would work and where I can find a reference for this
type of thing. I have another that lists SS#s similarly. Thanks.

Nick
 
Nick,

In my experience, a currency data type field will automatically
display in currency format in a combobox or listbox.

Can you say some more about what you want to do with the SS#?

- Steve Schapel, Microsoft Access MVP
 
Thanks for helping with this. There is no table field for the currency.
The listbox uses a query with calculated fields.

The ss# is pulled into a word doc as a variable that goes into a text form
field. Along the way, it loses its dashes.

Nick
 
Nick,

OK, thanks for the further clarification.

Here's an example of how your calculated field might look in the
query...
Amount: Format([Quantity]*[Price],"Currency")

I have no idea what a ss# looks like, but here is an example of
putting a dash within a number...
DashedNo: Left([SS],3) & "-" & Mid([SS],4)
So, if SS is 98765 then DashedNo will be 987-65

- Steve Schapel, Microsoft Access MVP
 
Thank you Steve. That worked perfectly. Can I add one small omission on my
part?

Is it possible to get the currency column to align right in a listbox? This
way the decimals will line up and it will be easier to read. btw, by ss# I
meant social security number nnn-nn-nnnn

Thanks again.



Steve Schapel said:
Nick,

OK, thanks for the further clarification.

Here's an example of how your calculated field might look in the
query...
Amount: Format([Quantity]*[Price],"Currency")

I have no idea what a ss# looks like, but here is an example of
putting a dash within a number...
DashedNo: Left([SS],3) & "-" & Mid([SS],4)
So, if SS is 98765 then DashedNo will be 987-65

- Steve Schapel, Microsoft Access MVP


Thanks for helping with this. There is no table field for the currency.
The listbox uses a query with calculated fields.

The ss# is pulled into a word doc as a variable that goes into a text form
field. Along the way, it loses its dashes.

Nick
Click to expand...
Click to expand...
 
See:
http://www.lebans.com/justicombo.htm
New Ver 2.1 Justification.zip is a database containing functions to
center and right justify data for List and Combo Boxes.

Major changes for Version 2.1 are:
Use standard API's instead of functions exposed in Access EXE.

Handle Multiple Columns

One Function for both Center and Right Alignment

Support for controls on SubForms

Huge increase in function execution speed

Clean code with comments

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Nick Mirro said:
Thank you Steve. That worked perfectly. Can I add one small omission on my
part?

Is it possible to get the currency column to align right in a listbox? This
way the decimals will line up and it will be easier to read. btw, by ss# I
meant social security number nnn-nn-nnnn

Thanks again.



Steve Schapel said:
Nick,

OK, thanks for the further clarification.

Here's an example of how your calculated field might look in the
query...
Amount: Format([Quantity]*[Price],"Currency")

I have no idea what a ss# looks like, but here is an example of
putting a dash within a number...
DashedNo: Left([SS],3) & "-" & Mid([SS],4)
So, if SS is 98765 then DashedNo will be 987-65

- Steve Schapel, Microsoft Access MVP


Thanks for helping with this. There is no table field for the currency.
The listbox uses a query with calculated fields.

The ss# is pulled into a word doc as a variable that goes into a
Click to expand...
Click to expand...
text
form
Click to expand...
Click to expand...
 
Nick,

[...] btw, by ss# I
meant social security number nnn-nn-nnnn
Click to expand...

Please remember that the newsgroup is an international communty :-)

So...
DashedNo: Left([SS],3) & "-" & Mid([SS],4,2) & "-" & Mid([SS],6)

By the way, I don't know if you mean your existing field is named ss#
but if so, it is not a good idea to include a # as part of the name of
a field or database object.

- Steve Schapel, Microsoft Access MVP
 
Back
Top