Total in Continous Form

I

Ira

I have a rather complicated continuous form that calculates chemical
formulations. One of my fields calculates gallons based upon other
variables See below.

=IIf([Weight]=0,0,IIf([UnitOfMeasure]="lbs",IIf([LbsPerGallon]>0,[Qty]/
[LbsPerGallon],0),[Qty]))


It is a continuos form with each line representing the necessary
gallons of each different ingredient.

I can't this particular field to total correctly. I know totals can be
tricky (page footer/form footer) on continuous forms. The sum()
function is not working correcly. I keep getting ERROR. Any help or
suggestions would be appreciated.

thanks.
 
A

Access Developer

Simplest way to avoid "unexpected occurrences" with calculations in
continuous forms is "don't do it"... create a calculated field containing
that result in the query that if the record source for the form.
 
I

Ira

Simplest way to avoid "unexpected occurrences" with calculations in
continuous forms is "don't do it"... create a calculated field containing
that result in the query that if the record source for the form.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access




I have a rather complicated continuous form that calculates chemical
formulations.  One of my fields calculates gallons based upon other
variables See below.
=IIf([Weight]=0,0,IIf([UnitOfMeasure]="lbs",IIf([LbsPerGallon]>0,[Qty]/
[LbsPerGallon],0),[Qty]))

It is a continuos form with each line representing the necessary
gallons of each different ingredient.
I can't this particular field to total correctly. I know totals can be
tricky (page footer/form footer) on continuous forms.  The sum()
function is not working correcly. I keep getting ERROR. Any help or
suggestions would be appreciated.
thanks.- Hide quoted text -

- Show quoted text -

You're right.

I created a query with the calculated field, used the SUM() on that
field and it worked perfectly.

Thanks Larry
 
M

Mike Painter

Ira said:
Simplest way to avoid "unexpected occurrences" with calculations in
continuous forms is "don't do it"... create a calculated field
containing that result in the query that if the record source for
the form.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley Access newsgroup support is alive and well in USENET
comp.databases.ms-access




I have a rather complicated continuous form that calculates chemical
formulations. One of my fields calculates gallons based upon other
variables See below.
=IIf([Weight]=0,0,IIf([UnitOfMeasure]="lbs",IIf([LbsPerGallon]>0,[Qty]/
[LbsPerGallon],0),[Qty]))

It is a continuos form with each line representing the necessary
gallons of each different ingredient.
I can't this particular field to total correctly. I know totals can
be tricky (page footer/form footer) on continuous forms. The sum()
function is not working correcly. I keep getting ERROR. Any help or
suggestions would be appreciated.
thanks.- Hide quoted text -

- Show quoted text -

You're right.

I created a query with the calculated field, used the SUM() on that
field and it worked perfectly.

Thanks Larry

Note that this is not exactly what was stated
"... create a calculated field containing that result in the query that is
the record source for the form. "

Instead of
"=IIf([Weight]=0,0,IIf([UnitOfMeasure]="lbs",IIf([LbsPerGallon]>0,[Qty]/
[LbsPerGallon],0),[Qty]))"

ON your form adding
"MyCalculationField:IIf([Weight]=0,0,IIf([UnitOfMeasure]="lbs",IIf([LbsPerGallon]>0,[Qty]/
[LbsPerGallon],0),[Qty]))"
"in the query that is the record source for the form." would usually be a
better solution.

That query can be used inother reports and forms, thus requiring less
coding.
 
A

Access Developer

Ira said:
I created a query with the calculated field,
used the SUM() on that field and it
worked perfectly.
Thanks Larry

As long as all the factors in the calculation are available, it's a good
approach.

If "emulating" an old sequential processing calculation requiring values
from "preceding" and "following" records, it's more complicated -- but
often, just taking a different view than "sequential processing" can solve
that, as well.

Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET at
comp.databases.ms-access
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top