UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formatting Currency, Access 2013    
 
   
eacollie
post Dec 4 2019, 11:46 AM
Post#1



Posts: 729
Joined: 15-January 05
From: Tennessee


I'm having trouble formatting for currency in a single textbox on a report and it may be due to the way the data is calculated (it won't format for currency).

[Meals} = Nz(Sum([NumberOfPersons]*[PerPersonCost]),0)
where [NumberOfPersons] is a long integer and [PerPersonCost] is a double with currency format.

Is it due to the product of a long integer multiplied by a double, and if so what is the workaround?

Thanks!
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2019, 11:50 AM
Post#2


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Wouldn't this work:

[Meals] = Format(Nz(Sum([NumberOfPersons]*[PerPersonCost]),0), "Currency")

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
eacollie
post Dec 4 2019, 11:53 AM
Post#3



Posts: 729
Joined: 15-January 05
From: Tennessee


Yes, but then I have issues with using this formatted value in a calculation in the query.
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2019, 12:06 PM
Post#4


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


There's a follow on calculation after this one?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
eacollie
post Dec 4 2019, 12:17 PM
Post#5



Posts: 729
Joined: 15-January 05
From: Tennessee


I'm using this value in another calculation in the same query.
I've found a workaround -- using the non-formatted value in the calculation and the formatted value for the report - this seems to work.
I suspect something happens when I multiply an integer by a double, but can't find anything to explain what it is. Thought someone here may know.

Thanks!
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2019, 12:36 PM
Post#6


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Formatting is a separate property, so this is quite predictable.

Think of it this way, if you will. All of these values are the same:

0100
100
100.00
$100
$100.00

They only look different because of formatting.

In your case, formatting is the FINAL step that makes the calculation look nice for the user. Formatting doesn't change the value itself.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Dec 4 2019, 12:40 PM
Post#7


UtterAccess VIP
Posts: 11,285
Joined: 10-February 04
From: South Charleston, WV


You might want to make a copy of you db and change the double to decimal with 5 or 6 decimal points / scale and then try using that and see what happens.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 09:50 AM