Group Footer Calculations

[Brennan]

Hello:

I have the following controls in a group footer section
of a report:

Award Ratio-Format is General Number
Pending Bids-Format is Currency

I need to do a simple calculation that multiplies the
Award Ratio control by the Pending Bids control to give
me a figure known as the Pipeline amount.

The report runs but the math isn't correct. For example

Award Ratio = .11
Pending Bid = $1,050,000
Pipeline amount from Report= $117,954
Pipeline Amount when calculated manually = $115,500

I have spot checked the math on a number of records and
the sums are always higher than what they should be.

Any ideas on where I'm going wrong would be appreciated

Thanks,
Brennan

 

[Wayne Morgan]

What do you have for the Control Source of the textbox that this calculation is displayed
in?

 

[Wayne Morgan]

Are txtRatio and txtPendingYr3 the names of the textboxes that hold the values you
mentioned (just want to rule out typos)? I'm aware of getting round-off errors sometimes,
but this seems extreme.

What happens if you try

=CCur([txtRatio])*CCur([txtPendingYr3])

Is there anything else with the names you have mentioned (i.e. field names)? Where do
txtRatio and txtPendingYr3 get their values? If you pull directly from those sources and
do the entire calculation in the last text box instead of using the other 2 intermediate
boxes what happens?

--
Wayne Morgan
Microsoft Access MVP

I have the following code:

= [txtRatio]*[txtPendingYr3]

Brennan

-----Original Message-----
What do you have for the Control Source of the textbox

that this calculation is displayed

in?

--
Wayne Morgan
Microsoft Access MVP





.

 

[Brennan]

I tried your CCur suggestion and it generated a #error.
I also tried

There are no other fields with the names. txtRatio gets
it's value by dividing the control containing the sum of
all Awarded Bids by a control that contains the sum of
all bids for a 4 year period. txtPendingYr3 gets it's
value from the detail section for the Pending bids. The
idea is to get a rolling average of the percentage of
bids awarded over the 4 year and to apply that percentage
to Pending Bids to give the contractor an idea of what he
can expect to be awrded based upon his past performance.

The underlying datasource for the report is a crosstab
query that presents the data on an annual basis in a
format required by the contractor.

I don't get how it's increasing the amount.

Brennan

-----Original Message-----
Are txtRatio and txtPendingYr3 the names of the

textboxes that hold the values you

mentioned (just want to rule out typos)? I'm aware of

getting round-off errors sometimes,

but this seems extreme.

What happens if you try

=CCur([txtRatio])*CCur([txtPendingYr3])

Is there anything else with the names you have mentioned (i.e. field names)? Where do
txtRatio and txtPendingYr3 get their values? If you pull

directly from those sources and

do the entire calculation in the last text box instead

of using the other 2 intermediate

boxes what happens?

--
Wayne Morgan
Microsoft Access MVP

I have the following code:

= [txtRatio]*[txtPendingYr3]

Brennan

-----Original Message-----
What do you have for the Control Source of the textbox

that this calculation is displayed

in?

--
Wayne Morgan
Microsoft Access MVP


"Brennan" <[email protected]> wrote

in
message

Hello:

I have the following controls in a group footer section
of a report:

Award Ratio-Format is General Number
Pending Bids-Format is Currency

I need to do a simple calculation that multiplies the
Award Ratio control by the Pending Bids control to give
me a figure known as the Pipeline amount.

The report runs but the math isn't correct. For example

Award Ratio = .11
Pending Bid = $1,050,000
Pipeline amount from Report= $117,954
Pipeline Amount when calculated manually = $115,500

I have spot checked the math on a number of records and
the sums are always higher than what they should be.

Any ideas on where I'm going wrong would be appreciated

Thanks,
Brennan


.

.

 

[Wayne Morgan]

I really don't either.

What happens if you do the calculation in the Format event of the footer and place it in
the textbox?

 

[Brennan]

Thanks for all your help Wayne:

This is definitely a rounding issue. As an additional
check, I divided the figure I'm getting as the Pipeline
Amount by the Pending Bid Amount. In the example given
below, I manually divided $117,954 by $1,050,000. The
answer I get is .1123371. If I just mutliply by the .11,
I'm getting a different value.

I'm stumped

-----Original Message-----
I really don't either.

What happens if you do the calculation in the Format

event of the footer and place it in

 

[Wayne Morgan]

Ok, so the value in the ratio textbox is probably being limited by the number of decimals
you have told it to display, but perhaps the value is actually larger? Where does this
value come from, is it calculated as well? Is there a chance that it should be larger and
just isn't being displayed due to formatting the result to 2 decimals? If it should be
larger, based on the calculation, but you want to limit it to the 2 decimals for the next
calculation, that can be done, or it can be left as is, which is probably more accurate if
that is what is happening.