?
Solved

Field on report won't display commas

Posted on 2011-10-25
8
Medium Priority
?
252 Views
Last Modified: 2013-11-05
I have a number field on a report set as standard, 0 decimals.  But it will not display the commas.

Here is the control source.  How can I change it to display commas (like for example...  5,250)

=Nz([QtyAvail],0)

--Steve
0
Comment
Question by:SteveL13
  • 4
  • 2
  • 2
8 Comments
 
LVL 49

Accepted Solution

by:
Dale Fye earned 1000 total points
ID: 37026070
Try forcing it to a numeric format:

=CLNG(NZ([QtyAvail], 0))

0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 37026076
How about

=Format(Nz([QtyAvail],0),"0,000")

mx
0
 
LVL 75
ID: 37026102
This is probably better:

=IIF(IsNull([QtyAvail]), Null, Format([QtyAvail],"0,000") )

mx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:HainKurt
ID: 37026135
no need to use NZ or IsNull

= format([QtyAvail], "0,000")

should be ok, if it is null, then format returns null (or empty string)
0
 
LVL 75
ID: 37026165
But ...

Format(0,"0,000")
Returns 0,000

fyed's solution is probably the best.

mx
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37026241
I've found that Access frequently fails to correctly interpret the desired datatype returned by the NZ() function, so I normally do this type of explicit typing whenever I use NZ().
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 37028503
0
 
LVL 75
ID: 37028526
Yeah, I guess that was obvious, lol ...

mx
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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