Solved

How format numbers differently in a listbox

Posted on 2014-04-26
4
424 Views
Last Modified: 2014-04-27
I have this SQL code in a query which drops the decimals from the number which is exactly what I want to do:

SELECT tblEstParts.EstPartID, tblEstParts.PartDesc, tblPaper.Description, tblEstParts.InkColors, Format([tblEstParts].[Qty1],"0") AS [Qty 1], Format([tblEstParts].[Qty2],"0") AS [Qty 2], Format([tblEstParts].[Qty3],"0") AS [Qty 3], Format([tblEstParts].[Qty4],"0") AS [Qty 4], Format([tblEstParts].[Qty5],"0") AS [Qty 5]
FROM tblEstParts LEFT JOIN tblPaper ON tblEstParts.PaperID = tblPaper.PaperID
ORDER BY tblEstParts.PartDesc;

But I also want the numbers to have a comma if for example the number is 2500 I want the listbox field to display 2,500

How can this be done?

--Steve
0
Comment
Question by:SteveL13
4 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 40024620
try something like this

Format([tblEstParts].[Qty3],"#,###")
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 40024735
Actually, all values in a listbox are formatted like text, so if you want the numbers to align to the right side of a column, you will have to set the font to a non-proportional font (I like Consolas, but Courier New works too), and will then need to pad the string to however many characters you want.  So try something like:

Right(Space(8) & Format([tblEstParts].Qty3, "#,###"), 8)

HTH

Dale
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 40024932
Change your format.  Format([tblEstParts].[Qty1],"#,##0")

The "0" in use means that at least one numerical character will be displayed with no decimal place.

The "#,##0" means that additional places can be displayed add the thousands separator (",") and still no decimal places.

Since you are  using this on a numeric field, I don't think the text comment applies.  The field should stay numeric.  Please correct me if I am wrong.  The listbox that this populates is written in with what language?   Some languages have separate listbox properties for alignment.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40025580
List- and comboboxes always display text, thus numbers will be left aligned.
Further, the output from Format is text, so even if numbers were right aligned it wouldn't make a difference.

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question