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
> My Iif Statement Will Not Display As A Percentage., Access 2016    
 
   
leadbewj
post Aug 10 2018, 08:42 PM
Post#1



Posts: 3
Joined: 10-August 18



My original calculation is this %PSbSL: [PSbSL Tax]/[OC Total] however when [OC Total] = 0 it returns #Num!
If I change it to this%PSbSL: IIf([OC Total]=0,"0%",[PSbSL Tax]/[OC Total]) it will make a 0% for where [OC Total]=0 but it won't format the other numbers as a percentage.
Go to the top of the page
 
tina t
post Aug 10 2018, 09:13 PM
Post#2



Posts: 5,546
Joined: 11-November 10
From: SoCal, USA


if you're using the calculation in a query, see if the field (column in query design view) has a Format property that you can set. if you're using the calc in the ControlSource property of a control on a form, try setting the control's Format property.

if the Format property doesn't work, you can use the Format() function. note that the function returns a string value, so if you need to do further math calculations on the calculated value, you probably won't be able to use the Format() function.

hth
tina
Go to the top of the page
 
projecttoday
post Aug 10 2018, 09:26 PM
Post#3


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


First try changing it to IIf([OC Total]=0,0,[PSbSL Tax]/[OC Total]).
Go to the top of the page
 
leadbewj
post Aug 11 2018, 11:15 AM
Post#4



Posts: 3
Joined: 10-August 18



That did not work, Thanh you.
Go to the top of the page
 
projecttoday
post Aug 11 2018, 11:36 AM
Post#5


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


What did not work?

IIf([OC Total]=0,"0%",[PSbSL Tax]/[OC Total]) is going to return "0%" if OC Total is 0 and a number if not. That's mixing apples and oranges. So before you do what tina said and format the result you need to get rid of the string in the calculation.
Go to the top of the page
 
leadbewj
post Aug 11 2018, 12:18 PM
Post#6



Posts: 3
Joined: 10-August 18



I'm sorry your answer did work, I just forgot to reset the percentage in the formatting when I changed the formula. I see now where my issue was I has assigning it a string value using 0% rather than just using 0 and let the format function take care of the formatting. Sometimes we don't see the trees for the forest. Thank you very much
Go to the top of the page
 
projecttoday
post Aug 11 2018, 01:17 PM
Post#7


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


thumbup.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th October 2018 - 03:26 AM