Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-05-2019, 03:24 PM   #1
abenitez77
Newly Registered User
 
Join Date: Apr 2010
Posts: 127
Thanks: 5
Thanked 0 Times in 0 Posts
abenitez77 is on a distinguished road
Question formatting numbers

I have a report that is using a query where I have a column being formatted in the query like this:
CreditMemoAmt: "- " & CStr(Abs([CreditMemoAmount]))

In the report, numbers like 1,500.00 are coming across like 1500. And 1623,60 are 1623.6

How can i fix this? I want the numbers to appear like this:
-$1,500.00 or ($1,500.00)
- $1,623.60 or ($1,623.60)

thanks,
Alex

abenitez77 is offline   Reply With Quote
Old 11-05-2019, 03:40 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,387
Thanks: 13
Thanked 4,123 Times in 4,055 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: formatting numbers

Try

Format(Abs([CreditMemoAmount]), "Currency")
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 11-05-2019, 05:05 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,716
Thanks: 105
Thanked 1,511 Times in 1,424 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: formatting numbers

Code:
Format(Abs([CreditMemoAmount] "($#,###.00)")
Code:
Format(Abs([CreditMemoAmount] "- $#,###.00")

Galaxiom is offline   Reply With Quote
Old 11-06-2019, 09:39 AM   #4
abenitez77
Newly Registered User
 
Join Date: Apr 2010
Posts: 127
Thanks: 5
Thanked 0 Times in 0 Posts
abenitez77 is on a distinguished road
Re: formatting numbers

Quote:
Originally Posted by Galaxiom View Post
Code:
Format(Abs([CreditMemoAmount] "($#,###.00)")
Code:
Format(Abs([CreditMemoAmount] "- $#,###.00")

This worked! I just had to add a missing comma and parens:

Code:
Format(Abs([CreditMemoAmount]), "- $#,###.00")
Thanks!
abenitez77 is offline   Reply With Quote
Old 11-06-2019, 01:23 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,716
Thanks: 105
Thanked 1,511 Times in 1,424 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: formatting numbers

Quote:
Originally Posted by abenitez77 View Post
This worked! I just had to add a missing comma and parens:

Code:
Format(Abs([CreditMemoAmount]), "- $#,###.00")
Thanks!
Whoops, I pasted the name of your field in and must have accidentally got the missing characters when I selected what I replaced.

Glad it didn't stop you succeeding.

Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting numbers wtrimble Tables 6 12-04-2009 04:05 PM
Re formatting numbers NickNeville Tables 3 05-26-2009 04:34 AM
Formatting numbers VegaLA Queries 1 10-25-2006 10:23 AM
Formatting Numbers xDamian General 2 02-12-2004 01:52 AM
Formatting Numbers marchristensen Reports 4 11-02-2002 08:00 PM




All times are GMT -8. The time now is 02:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World