formula works in one form & not in another

[JulieD]

hi

i'm looking at a rather complex db for a friend - she has a form called
frmShipmentGradesbyProduct(Loaded) - its a continuous form with a number of
fields (i'm only interested in 3)
record source of the form is a query
DMT (control source DMT)
Moisture (control source Moisture)
WeightedAverage (control source =Sum([DMT]*[Moisture])/Sum([DMT]) )

and the formula appears to work just fine

she has another form called frmShipmentGradesbyProduct(Discharged) -
basically the same situation as the last form - three fields
DischargedDMT (control source DischargedDMT)
Moisture (control source DischargedMoisture)
WeightedAverage (control source
=Sum([dischargedDMT]*[moisture])/Sum([dischargedDMT]))
this formula returns a #ERROR

the only difference that i can spot in the forms is that the record source
of the second one comes from two tables, while the first one comes only from
one (but it appears to be linked correctly)

any ideas?

 

[Wayne Morgan]

In the second ones, are the fields bound to controls on the form? If so, do
the controls have the same name as the field? If so, try changing the names
of the controls, (i.e. change the textbox for the field dischargedDMT to
txtDischargedDMT). This will sometime solve a "circular reference" problem
with two objects having the same name and is a good operating practice even
if it doesn't cause a problem in this case.

Is there a chance that dischargedDMT is 0? If so, you are dividing by zero.

You mention that the data in the second form comes from a query tying two
tables together. Are either of the fields mentioned available in both
tables? If so, have they both been included in the query's output? If so,
they probably are actually named Table1.dischargedDMT and
Table2.dischargedDMT. The query will include the table name to differentiate
between the two if a field with the same name in more than one table is
included in the query's output.

 

[JulieD]

Hi Wayne

thanks for your timely response - my comments are inline below

In the second ones, are the fields bound to controls on the form? If so, do
the controls have the same name as the field? If so, try changing the names
of the controls, (i.e. change the textbox for the field dischargedDMT to
txtDischargedDMT). This will sometime solve a "circular reference" problem
with two objects having the same name and is a good operating practice even
if it doesn't cause a problem in this case.

changed the names - rebuilt control source with expression builder to no
avail

Is there a chance that dischargedDMT is 0? If so, you are dividing by zero.

no ... checked in underlying query all seems fine

You mention that the data in the second form comes from a query tying two
tables together. Are either of the fields mentioned available in both
tables? If so, have they both been included in the query's output? If so,
they probably are actually named Table1.dischargedDMT and
Table2.dischargedDMT. The query will include the table name to differentiate
between the two if a field with the same name in more than one table is
included in the query's output.

nope - no common fields between the to (expect linking field )

the only think i found when i had a look is that DMT (in first form) and
dischargedDMT in (second form ) are both fields calculated in the underlying
query ... but when you look at the recordset returned from the query all
seems well

BUT when i put code like =IIf(IsError(Sum([DischargedDMT])),1,0)
0 flashes up on the screen and then the field changes to #Error

when i build a new form with just these three fields and use the original
=sum( etc syntax it works fine
could this indicate a problem with this form & is there any alternative to
killing it and re-creating it?


Cheers
JulieD

--
Wayne Morgan
Microsoft Access MVP

hi

i'm looking at a rather complex db for a friend - she has a form called
frmShipmentGradesbyProduct(Loaded) - its a continuous form with a number of
fields (i'm only interested in 3)
record source of the form is a query
DMT (control source DMT)
Moisture (control source Moisture)
WeightedAverage (control source =Sum([DMT]*[Moisture])/Sum([DMT]) )

and the formula appears to work just fine

she has another form called frmShipmentGradesbyProduct(Discharged) -
basically the same situation as the last form - three fields
DischargedDMT (control source DischargedDMT)
Moisture (control source DischargedMoisture)
WeightedAverage (control source
=Sum([dischargedDMT]*[moisture])/Sum([dischargedDMT]))
this formula returns a #ERROR

the only difference that i can spot in the forms is that the record source
of the second one comes from two tables, while the first one comes only from
one (but it appears to be linked correctly)

any ideas?

 

[Wayne Morgan]

If the data in the query's output is correct, I'm at a loss. I wouldn't
recreate the form without trying a new form linked to the second query with
just enough controls on it to see if it will do the calculation. If that
works, then there is a possibility that the form is corrupted. It appears
you've done this, so yes, there is a chance that the form is corrupted. As
you build the new form, try adding one control at a time to see if there is
one that interferes.

Are any of the fields "lookup fields"? If so, what you see may not be what
the actual value is (I don't think so in this case, but worth checking).
What happens if you try just the inner sum? If that works, try making a
hidden textbox with just that sum then use the value of that textbox in the
denominator.

--
Wayne Morgan
Microsoft Access MVP

Hi Wayne

thanks for your timely response - my comments are inline below

In the second ones, are the fields bound to controls on the form? If so, do
the controls have the same name as the field? If so, try changing the names
of the controls, (i.e. change the textbox for the field dischargedDMT to
txtDischargedDMT). This will sometime solve a "circular reference" problem
with two objects having the same name and is a good operating practice even
if it doesn't cause a problem in this case.

changed the names - rebuilt control source with expression builder to no
avail

Is there a chance that dischargedDMT is 0? If so, you are dividing by zero.

no ... checked in underlying query all seems fine

You mention that the data in the second form comes from a query tying two
tables together. Are either of the fields mentioned available in both
tables? If so, have they both been included in the query's output? If so,
they probably are actually named Table1.dischargedDMT and
Table2.dischargedDMT. The query will include the table name to differentiate
between the two if a field with the same name in more than one table is
included in the query's output.

nope - no common fields between the to (expect linking field )

the only think i found when i had a look is that DMT (in first form) and
dischargedDMT in (second form ) are both fields calculated in the underlying
query ... but when you look at the recordset returned from the query all
seems well

BUT when i put code like =IIf(IsError(Sum([DischargedDMT])),1,0)
0 flashes up on the screen and then the field changes to #Error

when i build a new form with just these three fields and use the original
=sum( etc syntax it works fine
could this indicate a problem with this form & is there any alternative to
killing it and re-creating it?

 

[JulieD]

hi wayne

as the db is on a tight schedule we're going down the path of recreating the
form - the thing that i find really odd is that the IIF statement showed
that it was being evaluated before changing to #Error - which looks to me
like a corruption.

thanks for your assistance.

regards
JulieD

If the data in the query's output is correct, I'm at a loss. I wouldn't
recreate the form without trying a new form linked to the second query with
just enough controls on it to see if it will do the calculation. If that
works, then there is a possibility that the form is corrupted. It appears
you've done this, so yes, there is a chance that the form is corrupted. As
you build the new form, try adding one control at a time to see if there is
one that interferes.

Are any of the fields "lookup fields"? If so, what you see may not be what
the actual value is (I don't think so in this case, but worth checking).
What happens if you try just the inner sum? If that works, try making a
hidden textbox with just that sum then use the value of that textbox in the
denominator.

--
Wayne Morgan
Microsoft Access MVP

Hi Wayne

thanks for your timely response - my comments are inline below

In the second ones, are the fields bound to controls on the form? If

so,
do

the controls have the same name as the field? If so, try changing the names
of the controls, (i.e. change the textbox for the field dischargedDMT to
txtDischargedDMT). This will sometime solve a "circular reference" problem
with two objects having the same name and is a good operating practice even
if it doesn't cause a problem in this case.

changed the names - rebuilt control source with expression builder to no
avail

Is there a chance that dischargedDMT is 0? If so, you are dividing by zero.

no ... checked in underlying query all seems fine

You mention that the data in the second form comes from a query tying two
tables together. Are either of the fields mentioned available in both
tables? If so, have they both been included in the query's output? If so,
they probably are actually named Table1.dischargedDMT and
Table2.dischargedDMT. The query will include the table name to differentiate
between the two if a field with the same name in more than one table is
included in the query's output.

nope - no common fields between the to (expect linking field )

the only think i found when i had a look is that DMT (in first form) and
dischargedDMT in (second form ) are both fields calculated in the underlying
query ... but when you look at the recordset returned from the query all
seems well

BUT when i put code like =IIf(IsError(Sum([DischargedDMT])),1,0)
0 flashes up on the screen and then the field changes to #Error

when i build a new form with just these three fields and use the original
=sum( etc syntax it works fine
could this indicate a problem with this form & is there any alternative to
killing it and re-creating it?