Percentage Calculation in Query 1

[JustLJ]

Greetings.

I'm embarrassed to put this out here, but the brain is fried and I'm obviously not seeing the obvious...

In a query, used as the basis for both other tables and for reports, I need to calculate percentages, like the percent complete (using two numeric fields, which could have decimal entries). I'm using the Design View for the query and creating my own field, as thus:

CalcPrctComplete: IIf([MainTable]![Estimated_Hours]>0 And [MainTable]![Actual_Hours]>0,iif( (round([MainTable]![Actual_Hours])/([MainTable]![Estimated_Hours])*100)>99,100,(round([MainTable]![Actual_Hours])/([MainTable]![Estimated_Hours])*100)),0)

My problem is I get results like 27.272727 or 83.33333 and all I want is the whole number (i.e. 27 or 83). Since some things will be zero and some things 100, I don't think I can use rTrim or Len type of functions. I can't find a "whole" or "percent" type of function on the built in list to specify no decimal places.

Any thoughts about being able to have the results be a whole (no decimal place) number?

Thanks!
LJ

 

[sfreeman]

This is a very simplied fix:

SELECT
Table1.FirstNum
, Table1.SecNum
, CInt([secnum]/[firstnum]*100) AS Percentage

FROM
Table1;

 

[JustLJ]

Thanks sfreeman, I appreciate the prompt response.

I failed to mention that there are 52 other fields selected and checked ("iif'd") in the query. I'm not sure where the "field name for output" is in your example (which is my CalcPrctComplete in my example), nor how I'd squeeze this into the existing SQL (because obviously this doesn't go in the Design View that I'm using).

Thanks again for the help!
LJ

 

[sfreeman]

Really, you just need to enclose your calculation within the CInt function. That will convert whatever result you are getting ie: 27.99999 to 29.

 

[JustLJ]

Thanks! I took the CINT function on the design line and it worked great!

Thanks.

Star for you.