Aggregate rounding problem 3

[knuckelhead]

I give up. I have a Qry with a Sum on that totals line for StdQryAct field. I wanted 2 decimals. instead, I get 9 decimals.

The table is a number field, fixed 2.

So i tried the following in order to use the Round function but i still get 9 decimals instead of the desired 2.

I have the word "Expression" on the Totals line:
Std Qty Act: Sum(Round(nz([StdQtyAct]),2))

ideas?

 

[dhookom]

Is there a reason why you don't just format the results in your form or report? Also, don't use Nz() without two arguments.
Std Qty Act: Sum(Round(nz([StdQtyAct],0),2))
You might also want to use the Round() outside the Sum() or in the control source of the control on your form or report.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[knuckelhead]

Duane - I tried your method. did not know about putting "0" with the nz stuff. so thanks for that. i will use in future.

I get the same bad answer with many decimals. I
i now have in my query:
Std Qty Act: Round(Sum(Val(nz([StdQtyAct],0))),2)

it gives me an answer of 919774.130000001

I may look like i am nitpicking about the decimal 1 at the end. but sometimes there are more unwanted decimals.

any ideas?
Knucklehead

 

[knuckelhead]

I am starting to think that I need to first fix the initial Qry which contains:

StdQtyAct: Val(Round(nz([SellQtyAct])*(nz([SellUnitAct])*nz([StdConvFactorAct])),2))

it does not have the nice code as Duane. what would be the best way to fix this statement?
Knucklehead

 

[knuckelhead]

oops. i made a mistake a moment ago in my renaming of the field. i will get back to you.

 

[knuckelhead]

I had the calc just fine afterall. so the question stands. can you improve my first query?

StdQtyAct: Val(Round(nz([SellQtyAct])*(nz([SellUnitAct])*nz([StdConvFactorAct])),2))


thanks

 

[knuckelhead]

Following duane's lead, I changed the first Qry to:

StdQtyAct: Val(Round(nz([SellQtyAct],2)*(nz([SellUnitAct],2)*nz([StdConvFactorAct],2)),2))

i get an answer of 919774.150000001

so still something odd getting more than 2 decimals?

 

[Golom]

Try

StdQtyAct: cCur(Round(nz([SellQtyAct],2)*(nz([SellUnitAct],2)*nz([StdConvFactorAct],2)),2))

You don't need the "Val" because "Round" already returns a numeric value.

Why are you defaulting all your fields to "2" when they contain NULL?


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jebry]

Hi!

In the design view of a query you can select a field and then right click on it. In the popup menu choose properties. Assuming Access recognizes it as a number field you should see a Format line and a Decimals line. In the Format line choose fixed and in the Decimals line put 2.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[knuckelhead]

Jeff- yes i know about the properties thing. my concern is not how the query shows. i did not want the many decimals to hang around for other calculations. i just want 2 decimals. the properties thing does not get rid of decimals over 2 decimals. the properties only fix the display, as you know.

so any other idea?

 

[PHV]

Perhaps this ?
StdQtyAct: Round(Nz([SellQtyAct],0)*Nz([SellUnitAct],0)*Nz([StdConvFactorAct],0),2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knuckelhead]

Hi - just got back. thanks so much to all of you for this help. I just tried the method by PHV, my favorite fellow. but it still does not work.

here is an example. the sellqryact is 250, sellUnitAct is 1 and sellConvfactor is .0375. they all get multiplied:
250*1*.0375 which should equal 9.38 if i wanted 2 decimals.
these 3 fields are in a table as a number field.

but i get 9.38000011444 for the StdQtyAct using the above PHV method. crazy isn't it?

I hate to bother you all but any ideas? but whatever we reliable method that we come up with can be used in more situations for me.
Knucklehead

 

[knuckelhead]

Hold the presses. i added something to the PHV method and got the proper 9.38 answer. so it works now for the first query. i added the VAL statement to the PHV statement.

StdQtyAct: Val(Round(Nz([SellQtyAct],0)*Nz([SellUnitAct],0)*Nz([StdConvFactorAct],0),2))

i am so happy.

now my next test is to see how this good query will work in the second Qry. i'll let you know soon.
thanks
Knuckelhead

 

[knuckelhead]

I guess i will live with the latter calc:
StdQtyAct: Val(Round(Nz([SellQtyAct],0)*Nz([SellUnitAct],0)*Nz([StdConvFactorAct],0),2))

Each Detail Qry1 32,000 records are perfect to the 2nd decimal.

But the Qry2, which sums up Qry1, has 10 of the 12 category summed records Accurately to the 2nd decimal whereas 2 category records have a decimal in the 9th place.

SPECIALTY CASE 81179.9699999999
SPECIFIC 919774.150000001

even if i make the qry2 field property at fixed 2 decimals, that is just for show. when i click on these 2 records, i still see the many decimals. odd.

i am willing to live with the many decimals. maybe it is an Access bug?

 

[knuckelhead]

Based on all I learned today, here is 2 alternative calculations which derives the same answer. I wonder which of the 2 methods you all prefer ??

Stage1: Val(IIf(nz([StagingMH3],0)=0,0,Round(Nz([BatchQty],0)*Nz([StagingMH3],0),2)))


Stage2: IIf(Val([StagingMH3] & "")=0,0,Round(Nz
([BatchQty],0)*Nz([StagingMH3],0),2))


they get the same answer. but maybe one is better than the other??