Unwanted Rounding in unbound control on Report

[dgunner71]

I have a report with 2 fields - [numTotalItemsPu rchased] and [curTotalCost]. When I try to calculate the average cost as [curTotalCost]/[numTotalItemsPu rchased], Access keeps rounding the value.

I am not doing this in VBA, I'm setting this as the control source for this calculated control. The control is set to Standard Format with 2 Decimals.

So to illustrate my dilemma, I purchased 5,500 widgets for $62,500.00. The value Access returns is $11.00. I cannot get it to correctly display $11.36.

Any help is appreciated.

Kind Regards.

Gunner

[jimatqsi]

Gunner,
Are you saying the math is done in a query? Or is some textbox bound to a formula like =a/b where a and b come from the recordsource? Are these two field names, [curTotalCost] and [numTotalItemsPu rchased],the names of textboxes?

What is the definition of the relevant fields in the table where this data comes from? I mean if [curTotalCost] and [numTotalItemsPu rchased] are text boxes bound to some fields from a table, how are those fields defined in the table?

Jim

[dgunner71]

Jim -

The report is fairly complex (this is the coup de grace of their system).

The report itself is bound to a query which links several tables.

The field [curTotalCost] is a calculated Currency value from a separate query (i.e. in the Query, this field = [curCosts]+[curFees]. I am using a DSum to pull this value in the report. The DSum function is included as the direct Control Source for this control. This value shows decimals just fine.

The field [numTotalItemsPu rchased] is similar to the previous value except it is not calculated in the query. The Control source of the control is a DSum from the same query as previously noted (separate from the report's control source). Because this only uses whole numbers, there are no decimals.

Finally, the field I'm having trouble with ([curAvgPurPrice]) is an unbound control (no underlying table) with the math included directly in the control source.

The math works perfectly except that it keeps rounding when I would I like to include decimals. On the format tab (for that control on the report), the Format is set to Currency with 2 decimals. I also tried to add a brand new control (thought maybe I was missing something) - the new control acts exactly the same.

Thanks again for your help, Jim.

Gunner

[dgunner71]

Jim -

Also - if I hard code these numbers to the field, it displays properly. So, if I change the control source from:

=IIf([fxSumofPurchases]>0,[fxCostofPurchases]\[fxSumofPurchases],"n/a")

to

=62500/5500

the field displays 11.36 correctly.

Not sure if that helps to eliminate a bunch of potential causes.

Thanks again for any assistance!

Gunner

[dgunner71]

Jim -

I've solved the problem although not with the best of options.

I have created 2 new controls [text182] and [text183].

[text182].ControlSource = [numTotalItemsPurchased]
[text183].ControlSource = [curTotalCost]
[curAvgPurPrice].ControlSource = [text183]/[text182]

As baffled as I am, it works perfectly!

I would still appreciate any insight into the problem if this makes any sense to anyone.

Thanks again for your assistance!

Gunner

[jimatqsi]

I was going to suggest exactly what you did. And also I thought you could try explicitly rounding your original formula, that is

IIf([fxSumofPurchases]>0,Round([fxCostofPurchases]\[fxSumofPurchases],2),"n/a")

or maybe use CDbl instead of Round.

Not that I can say why any of those should work and the original does not. I think the answer lies somewhere in the queries behind all this. Sometimes you just go with what works :) Congratulations on solving it.

Jim

[NeoPa]

If you look at the first line of code in post #4 you'll see that the division symbol used is "\" rather than "/".

\ ==> Integer divide. The result is always integral (whole numbers).
/ ==> Divide. Results vary, but this is what people generally understand by division.

[dgunner71]

NeoPa,

That was the cause - wow. Thanks for that great tip!

Thanks to all for the assistance!

[jimatqsi]

Old Eagle Eye Neopa :)

I didn't even know that operator was available. Always good to learn something.

Jim

[NeoPa]

I wish the old bit were as inaccurate as the eagle eye bit :-D