Solved

How Can I Make My Calculated Field Appear in a Given Format, for Example, No Decimal Places?

Posted on 2011-09-09
20
232 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:Rex85
  • 5
  • 5
  • 3
  • +3
20 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36513263
Use the Round() or Int() functions.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36513270
Round allows you to specify the # of decimals.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 36513272
or cast:

cast (iif(Total_QNs = 0 OR Total_Shipped = 0,0,  Total_QNs/Total_Shipped)  as numeric(18,0))
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36513275
just to be clear, CAST will not work within Access, but will if you are creating a pass through query to SQL Server.
0
 

Author Comment

by:Rex85
ID: 36513300
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
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 200 total points
ID: 36513309
You forgot the comma separating your IIF() from the 2nd parameter of Round().  Try:

 ROUND(iif(Total_QNs = 0 OR Total_Shipped = 0,0,  (Total_QNs/Total_Shipped)*1000000), 0) AS PPM

Is this all in Access, or are you using SQL Server?
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 150 total points
ID: 36513319
You may need to explicitly convert the output of IIf to a number (IIF returns a string).

Give this a try, too:

FormatNumber(iif(Total_QNs = 0 OR Total_Shipped = 0,0,  (Total_QNs/Total_Shipped)*1000000),0)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36513343
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 ().
0
 

Author Comment

by:Rex85
ID: 36513378
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
0
 

Author Comment

by:Rex85
ID: 36513431
Using Format Number as below...

FormatNumber(NZ(QPR_Interstuhl_QNs_tbl.Total_QNs,0),0) AS Total_QNs

I can control the number of decimal places, but the output is a string, not a number.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 75
ID: 36513554
"IIF returns a string)"
?

?IsNumeric(iif (1=1,101,200))
True

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36514427
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)
0
 
LVL 75
ID: 36514506
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36514530
I had read the text, but not the example in that KB article.

That is really funny!
0
 
LVL 75
ID: 36514546
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
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
ID: 36516016
If you wish to remove the decimals, use Int as that is native SQL:

IIf(Total_Shipped = 0, 0, Int(Total_QNs / Total_Shipped * 1000000)) AS PPM

/gustav
0
 

Author Comment

by:Rex85
ID: 36522744
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36522792
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
0
 

Author Closing Comment

by:Rex85
ID: 36523355
Thank you ALL very much.

Rex
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36524165
You are welcome!

/gustav
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now