Rex
asked on
How Can I Make My Calculated Field Appear in a Given Format, for Example, No Decimal Places?
One final refinement I'd like to make. I'd like this calculated field (I actually mulitplied it by 1,000,000 to get a PPM) to show without decimal places. I am going to make this a Make Table Query, so that Minitab can feed off of it, so I need the formatting to be part of the table that will be made.
Use the Round() or Int() functions.
Round allows you to specify the # of decimals.
or cast:
cast (iif(Total_QNs = 0 OR Total_Shipped = 0,0, Total_QNs/Total_Shipped) as numeric(18,0))
cast (iif(Total_QNs = 0 OR Total_Shipped = 0,0, Total_QNs/Total_Shipped) as numeric(18,0))
just to be clear, CAST will not work within Access, but will if you are creating a pass through query to SQL Server.
ASKER
fyed:
I tried the Round, but got Syntax Errors and it wouldn't Save. This is what I tried.
ROUND(iif(Total_QNs = 0 OR Total_Shipped = 0,0, (Total_QNs/Total_Shipped)* 1000000)0) AS PPM
I tried the Round, but got Syntax Errors and it wouldn't Save. This is what I tried.
ROUND(iif(Total_QNs = 0 OR Total_Shipped = 0,0, (Total_QNs/Total_Shipped)*
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FormatNumber?
mbizup, when did that come along? I've been using Access since 1995, and don't think I've ever seen a reference to that function.
I love it when I learn something new. And I like the ability to wrap negatives in ().
mbizup, when did that come along? I've been using Access since 1995, and don't think I've ever seen a reference to that function.
I love it when I learn something new. And I like the ability to wrap negatives in ().
ASKER
Fyed: This is all Access
mbizup: Weirdly, the output of the calcualtion (PPM) came out as a number, but the numerator and denominator fields came out as strings
mbizup: Weirdly, the output of the calcualtion (PPM) came out as a number, but the numerator and denominator fields came out as strings
ASKER
Using Format Number as below...
FormatNumber(NZ(QPR_Inters tuhl_QNs_t bl.Total_Q Ns,0),0) AS Total_QNs
I can control the number of decimal places, but the output is a string, not a number.
FormatNumber(NZ(QPR_Inters
I can control the number of decimal places, but the output is a string, not a number.
"IIF returns a string)"
?
?IsNumeric(iif (1=1,101,200))
True
mx
?
?IsNumeric(iif (1=1,101,200))
True
mx
Rex,
Have you tried Fyed's last post (http:#a36513319) ?
Fyed,
I'm not sure when it came around, but I've gotten a lot of use out of FormatNumber in reports, queries and forms. I first ran across it trying to put that () formatting for negatives in a combo box's drop down list. There are a few other neat related functions:
http://office.microsoft.com/en-us/access-help/formatnumber-function-HA001228842.aspx?CTT=3
The downside is that you may have to explicitly convert it to a number in some situations (it looks like Rex found one of those situations).
mx,
Is numeric does not necessarily mean a variable, field, etc is of a numeric data type, it simply means that something can be evaluated as a number.
http://office.microsoft.com/en-us/access-help/isnumeric-function-HA001228868.aspx
for example, s here is defined as a string, but isnumeric(s) is true, and I believe - not positive - you *may* have trouble using s as a number in some situations without an explicit type conversion (we definitely see a few questions here where strings that would pass an isDate() evaluation need to be wrapped in CDate() to make things work).
Dim s As String
s = " 1.25"
Debug.Print IsNumeric(s)
Have you tried Fyed's last post (http:#a36513319) ?
Fyed,
I'm not sure when it came around, but I've gotten a lot of use out of FormatNumber in reports, queries and forms. I first ran across it trying to put that () formatting for negatives in a combo box's drop down list. There are a few other neat related functions:
http://office.microsoft.com/en-us/access-help/formatnumber-function-HA001228842.aspx?CTT=3
The downside is that you may have to explicitly convert it to a number in some situations (it looks like Rex found one of those situations).
mx,
Is numeric does not necessarily mean a variable, field, etc is of a numeric data type, it simply means that something can be evaluated as a number.
http://office.microsoft.com/en-us/access-help/isnumeric-function-HA001228868.aspx
for example, s here is defined as a string, but isnumeric(s) is true, and I believe - not positive - you *may* have trouble using s as a number in some situations without an explicit type conversion (we definitely see a few questions here where strings that would pass an isDate() evaluation need to be wrapped in CDate() to make things work).
Dim s As String
s = " 1.25"
Debug.Print IsNumeric(s)
Yep ... forgot about that. Look at that horrible example in the KB:
"This example uses the IsNumeric function to determine if a variable can be evaluated as a number."
IsNumeric() is nowhere to be found in the example, lol !
mx
"This example uses the IsNumeric function to determine if a variable can be evaluated as a number."
IsNumeric() is nowhere to be found in the example, lol !
mx
I had read the text, but not the example in that KB article.
That is really funny!
That is really funny!
Maddening!
Also funny is that ... Help for IIF does not indicate what ... it returns, other than 'one of two parts '
But ... try this:
?vartype (iif (1=1,101,200))
2
2 = Integer
?vartype (iif (1=1,999999,200))
3
3 = Long Integer
?vartype (iif (1=1,"mbizup",200))
8
8 = String.
So apparently, it returns the appropriate Data Type ...?
mx
Also funny is that ... Help for IIF does not indicate what ... it returns, other than 'one of two parts '
But ... try this:
?vartype (iif (1=1,101,200))
2
2 = Integer
?vartype (iif (1=1,999999,200))
3
3 = Long Integer
?vartype (iif (1=1,"mbizup",200))
8
8 = String.
So apparently, it returns the appropriate Data Type ...?
mx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I get the same response whether I use Fyed's ROUND, Mbizup's FORMAT NUMBER, or Cactus Data's Int.
That appears to solve my question. I am confused, however, on how to award points.
Any suggestions on that? I really appreciate ALL of your help, and I don't want to offend anyone.
That appears to solve my question. I am confused, however, on how to award points.
Any suggestions on that? I really appreciate ALL of your help, and I don't want to offend anyone.
Split the points as you like - no big deal here - but please pay attention to the reduced formula of mine which, of course, could be used with Round as well.
Note that Int rounds down and is faster while the other functions perform 4/5 rounding (as far as I know), and that FormatNumber returns text which may not be what you need.
/gustav
Note that Int rounds down and is faster while the other functions perform 4/5 rounding (as far as I know), and that FormatNumber returns text which may not be what you need.
/gustav
ASKER
Thank you ALL very much.
Rex
Rex
You are welcome!
/gustav
/gustav