Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

How format numbers differently in a listbox

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
SteveL13
Asked:
SteveL13
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try something like this

Format([tblEstParts].[Qty3],"#,###")
0
 
Dale FyeCommented:
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
 
Richard DanekeCommented:
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
 
Gustav BrockCIOCommented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now