Calculations on a Continuous Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I'm trying to calculate the product of a group of textboxes and I keep
getting a #Name? error and I don't understand why. Hopefully someone can
point me in the right direction. I'm using Access 97.

The form is a Continuous Form.
In the Detail area of that form, I have a total of 9 columns (textboxes).
The values of 5 of those columns/textboxes are supplied by the Record Source
of the form (a query). The remaining 4 values are calculated values, mostly
division and multiplication of the various textboxes on that row.
In the Footer of that form, I'm doing a variety of calculations. Mostly
summing,
division, multiplication and one product.

To be clear, by product, I mean I want the values of a column multiplied
together (regardless of the number of rows in total) and the value displayed
in the textbox.

I previously posted a question regarding calculation of Product in Access.
I knew how to do it in Excel (simple, use PRODUCT(x) ) but I didn't know how
to do it in Access. I was told to use =Exp(Sum(Log([fieldname]))) as a
mathematical equivalent.

However, that value as a control source is causing my #Name? error. When I
remove that value, all other textboxes work as usual. When I put that value
in, it causes #Name? errors in all my =Sum Control Source values and #Error
in any calculated Control Source values in the Footer.

The exact code is as follows:
=Exp(Sum(Log([txtYield]))) where txtYield is the name of the textbox in the
Detail area of the form that I want to multiply together.

If anyone has any ideas on how to solve this, I would really appreciate it.
I'm at a complete loss, and I need to find a way to calculate the Product of
this column, to satisfy the data analysis requirements.

If I've left out anything, please let me know, and I'll do my best to clear
things up.

Thanks,
Jay

P.S. I'm out of work in 5 min, so I will not be able to respond to any
suggestions/questions until tomorrow.
 
the unbound control that contains the expression is not *also* named
txtYield, is it? if it is, change it - you can't use a circular reference in
Access.

i tried the expression you posted in a form, and it worked fine for me. i
tested in an A2000 db running in Acess2003, so that may be why, since you're
using A97. have you tried looking up the Exp() and Log() functions in Help
to see if they're documented in A97 - maybe one or both don't exist in that
version.

if you can't get it working, i suppose you could duplicate the result in VBA
by looping through a recordset and doing the math on each loop, carrying the
result from loop to loop.

hth
 
Hi Tina,

I think I was creating a circular reference, but if I am, I'm not sure how
to resolve it.

The Exp() and Log() functions are available in Access 97, so I do believe it
was this circular reference.

If you got it working (regardless of version) I'd like to know how you did
it. I'm not sure if there is a way around the circular reference without
going to VBA.

Here's the situation:

I have the following in the detail area.
Textbox named: txtDPO with a control source of DO_DEF_OPP (from a query)
Textbox named: txtYield with a control source of 1-[txtDPO]

In the footer, I want to get the product of the Yield textboxes. But I
don't know how to refer to the Yield textboxes in a continuous form. I tried
=Exp(Sum(Log([txtYield]))) because I thought it would loop through all the
txtYield textboxes for all the rows, but it appears not.

Maybe I do have to go to code, but I was hoping to avoid it.

Any other suggestions are appreciated. Maybe I've missed something, or
worst case, what I'm trying to do is impossible.

Thanks again,
Jay

tina said:
the unbound control that contains the expression is not *also* named
txtYield, is it? if it is, change it - you can't use a circular reference in
Access.

i tried the expression you posted in a form, and it worked fine for me. i
tested in an A2000 db running in Acess2003, so that may be why, since you're
using A97. have you tried looking up the Exp() and Log() functions in Help
to see if they're documented in A97 - maybe one or both don't exist in that
version.

if you can't get it working, i suppose you could duplicate the result in VBA
by looping through a recordset and doing the math on each loop, carrying the
result from loop to loop.

hth


Jay said:
Hi all,

I'm trying to calculate the product of a group of textboxes and I keep
getting a #Name? error and I don't understand why. Hopefully someone can
point me in the right direction. I'm using Access 97.

The form is a Continuous Form.
In the Detail area of that form, I have a total of 9 columns (textboxes).
The values of 5 of those columns/textboxes are supplied by the Record Source
of the form (a query). The remaining 4 values are calculated values, mostly
division and multiplication of the various textboxes on that row.
In the Footer of that form, I'm doing a variety of calculations. Mostly
summing,
division, multiplication and one product.

To be clear, by product, I mean I want the values of a column multiplied
together (regardless of the number of rows in total) and the value displayed
in the textbox.

I previously posted a question regarding calculation of Product in Access.
I knew how to do it in Excel (simple, use PRODUCT(x) ) but I didn't know how
to do it in Access. I was told to use =Exp(Sum(Log([fieldname]))) as a
mathematical equivalent.

However, that value as a control source is causing my #Name? error. When I
remove that value, all other textboxes work as usual. When I put that value
in, it causes #Name? errors in all my =Sum Control Source values and #Error
in any calculated Control Source values in the Footer.

The exact code is as follows:
=Exp(Sum(Log([txtYield]))) where txtYield is the name of the textbox in the
Detail area of the form that I want to multiply together.

If anyone has any ideas on how to solve this, I would really appreciate it.
I'm at a complete loss, and I need to find a way to calculate the Product of
this column, to satisfy the data analysis requirements.

If I've left out anything, please let me know, and I'll do my best to clear
things up.

Thanks,
Jay

P.S. I'm out of work in 5 min, so I will not be able to respond to any
suggestions/questions until tomorrow.
Click to expand...
Click to expand...
 
what is the name of the control in the footer, which has the expression
=Exp(Sum(Log([txtYield])))
as its' ControlSource? if the control name is txtYield, change it. if it's
not, then you don't have a circular reference.

as for the expression itself, it does work. as i said, when i tested it, it
worked fine for me. but wait, let's make sure i understand what you *expect*
it to do. i tested four records with the following values in one field, as

1
2
3
4

the expression returned 24. then i added a fifth record with the field value
of 5, and the expression returned 120. is that the result you're looking
for?

hth


Jay said:
Hi Tina,

I think I was creating a circular reference, but if I am, I'm not sure how
to resolve it.

The Exp() and Log() functions are available in Access 97, so I do believe it
was this circular reference.

If you got it working (regardless of version) I'd like to know how you did
it. I'm not sure if there is a way around the circular reference without
going to VBA.

Here's the situation:

I have the following in the detail area.
Textbox named: txtDPO with a control source of DO_DEF_OPP (from a query)
Textbox named: txtYield with a control source of 1-[txtDPO]

In the footer, I want to get the product of the Yield textboxes. But I
don't know how to refer to the Yield textboxes in a continuous form. I tried
=Exp(Sum(Log([txtYield]))) because I thought it would loop through all the
txtYield textboxes for all the rows, but it appears not.

Maybe I do have to go to code, but I was hoping to avoid it.

Any other suggestions are appreciated. Maybe I've missed something, or
worst case, what I'm trying to do is impossible.

Thanks again,
Jay

tina said:
the unbound control that contains the expression is not *also* named
txtYield, is it? if it is, change it - you can't use a circular reference in
Access.

i tried the expression you posted in a form, and it worked fine for me. i
tested in an A2000 db running in Acess2003, so that may be why, since you're
using A97. have you tried looking up the Exp() and Log() functions in Help
to see if they're documented in A97 - maybe one or both don't exist in that
version.

if you can't get it working, i suppose you could duplicate the result in VBA
by looping through a recordset and doing the math on each loop, carrying the
result from loop to loop.

hth


Jay said:
Hi all,

I'm trying to calculate the product of a group of textboxes and I keep
getting a #Name? error and I don't understand why. Hopefully someone can
point me in the right direction. I'm using Access 97.

The form is a Continuous Form.
In the Detail area of that form, I have a total of 9 columns (textboxes).
The values of 5 of those columns/textboxes are supplied by the Record Source
of the form (a query). The remaining 4 values are calculated values, mostly
division and multiplication of the various textboxes on that row.
In the Footer of that form, I'm doing a variety of calculations. Mostly
summing,
division, multiplication and one product.

To be clear, by product, I mean I want the values of a column multiplied
together (regardless of the number of rows in total) and the value displayed
in the textbox.

I previously posted a question regarding calculation of Product in Access.
I knew how to do it in Excel (simple, use PRODUCT(x) ) but I didn't
Click to expand...
know
how
to do it in Access. I was told to use =Exp(Sum(Log([fieldname]))) as a
mathematical equivalent.

However, that value as a control source is causing my #Name? error.
Click to expand...
When
I
remove that value, all other textboxes work as usual. When I put that value
in, it causes #Name? errors in all my =Sum Control Source values and #Error
in any calculated Control Source values in the Footer.

The exact code is as follows:
=Exp(Sum(Log([txtYield]))) where txtYield is the name of the textbox
Click to expand...
in
the
Detail area of the form that I want to multiply together.

If anyone has any ideas on how to solve this, I would really
Click to expand...
appreciate
it.
I'm at a complete loss, and I need to find a way to calculate the
Click to expand...
Product
of
this column, to satisfy the data analysis requirements.

If I've left out anything, please let me know, and I'll do my best to clear
things up.

Thanks,
Jay

P.S. I'm out of work in 5 min, so I will not be able to respond to any
suggestions/questions until tomorrow.
Click to expand...
Click to expand...
Click to expand...
 
Wow, quick reply!

The name of the textbox in the footer is txtOverallYield

And yes, that is the result I'm looking for.

Not sure what else to change. Unless it's a problem with A97. Even when I
try to simply Sum the textboxes (ignoring the longer expression) I get a
#Name? error. I'm wondering if I can't Sum calculated fields maybe? Not
really sure. I'm trying a few other things to see if I can figure it out.

Thanks,
Jay

tina said:
what is the name of the control in the footer, which has the expression
=Exp(Sum(Log([txtYield])))
as its' ControlSource? if the control name is txtYield, change it. if it's
not, then you don't have a circular reference.

as for the expression itself, it does work. as i said, when i tested it, it
worked fine for me. but wait, let's make sure i understand what you *expect*
it to do. i tested four records with the following values in one field, as

1
2
3
4

the expression returned 24. then i added a fifth record with the field value
of 5, and the expression returned 120. is that the result you're looking
for?

hth


Jay said:
Hi Tina,

I think I was creating a circular reference, but if I am, I'm not sure how
to resolve it.

The Exp() and Log() functions are available in Access 97, so I do believe it
was this circular reference.

If you got it working (regardless of version) I'd like to know how you did
it. I'm not sure if there is a way around the circular reference without
going to VBA.

Here's the situation:

I have the following in the detail area.
Textbox named: txtDPO with a control source of DO_DEF_OPP (from a query)
Textbox named: txtYield with a control source of 1-[txtDPO]

In the footer, I want to get the product of the Yield textboxes. But I
don't know how to refer to the Yield textboxes in a continuous form. I tried
=Exp(Sum(Log([txtYield]))) because I thought it would loop through all the
txtYield textboxes for all the rows, but it appears not.

Maybe I do have to go to code, but I was hoping to avoid it.

Any other suggestions are appreciated. Maybe I've missed something, or
worst case, what I'm trying to do is impossible.

Thanks again,
Jay

tina said:
the unbound control that contains the expression is not *also* named
txtYield, is it? if it is, change it - you can't use a circular reference in
Access.

i tried the expression you posted in a form, and it worked fine for me. i
tested in an A2000 db running in Acess2003, so that may be why, since you're
using A97. have you tried looking up the Exp() and Log() functions in Help
to see if they're documented in A97 - maybe one or both don't exist in that
version.

if you can't get it working, i suppose you could duplicate the result in VBA
by looping through a recordset and doing the math on each loop, carrying the
result from loop to loop.

hth


Hi all,

I'm trying to calculate the product of a group of textboxes and I keep
getting a #Name? error and I don't understand why. Hopefully someone can
point me in the right direction. I'm using Access 97.

The form is a Continuous Form.
In the Detail area of that form, I have a total of 9 columns (textboxes).
The values of 5 of those columns/textboxes are supplied by the Record
Source
of the form (a query). The remaining 4 values are calculated values,
mostly
division and multiplication of the various textboxes on that row.
In the Footer of that form, I'm doing a variety of calculations. Mostly
summing,
division, multiplication and one product.

To be clear, by product, I mean I want the values of a column multiplied
together (regardless of the number of rows in total) and the value
displayed
in the textbox.

I previously posted a question regarding calculation of Product in Access.
I knew how to do it in Excel (simple, use PRODUCT(x) ) but I didn't know
how
to do it in Access. I was told to use =Exp(Sum(Log([fieldname]))) as a
mathematical equivalent.

However, that value as a control source is causing my #Name? error. When
I
remove that value, all other textboxes work as usual. When I put that
value
in, it causes #Name? errors in all my =Sum Control Source values and
#Error
in any calculated Control Source values in the Footer.

The exact code is as follows:
=Exp(Sum(Log([txtYield]))) where txtYield is the name of the textbox in
the
Detail area of the form that I want to multiply together.

If anyone has any ideas on how to solve this, I would really appreciate
it.
I'm at a complete loss, and I need to find a way to calculate the Product
of
this column, to satisfy the data analysis requirements.

If I've left out anything, please let me know, and I'll do my best to
clear
things up.

Thanks,
Jay

P.S. I'm out of work in 5 min, so I will not be able to respond to any
suggestions/questions until tomorrow.
Click to expand...
Click to expand...
Click to expand...
 
Solved, sort of.

I rewrote the query to do the calculations in the query and not on the form.
Once I did that, and added an IIf for Is Null, the Control value works fine
now.

The value is as follows: =Exp(Sum(Log(IIf([Yield] Is Null,1,[Yield]))))

Thanks for your help though! I might have been limited due to Access 97.

Thanks again,
Jay

Jay said:
Wow, quick reply!

The name of the textbox in the footer is txtOverallYield

And yes, that is the result I'm looking for.

Not sure what else to change. Unless it's a problem with A97. Even when I
try to simply Sum the textboxes (ignoring the longer expression) I get a
#Name? error. I'm wondering if I can't Sum calculated fields maybe? Not
really sure. I'm trying a few other things to see if I can figure it out.

Thanks,
Jay

tina said:
what is the name of the control in the footer, which has the expression
=Exp(Sum(Log([txtYield])))
as its' ControlSource? if the control name is txtYield, change it. if it's
not, then you don't have a circular reference.

as for the expression itself, it does work. as i said, when i tested it, it
worked fine for me. but wait, let's make sure i understand what you *expect*
it to do. i tested four records with the following values in one field, as

1
2
3
4

the expression returned 24. then i added a fifth record with the field value
of 5, and the expression returned 120. is that the result you're looking
for?

hth


Jay said:
Hi Tina,

I think I was creating a circular reference, but if I am, I'm not sure how
to resolve it.

The Exp() and Log() functions are available in Access 97, so I do believe it
was this circular reference.

If you got it working (regardless of version) I'd like to know how you did
it. I'm not sure if there is a way around the circular reference without
going to VBA.

Here's the situation:

I have the following in the detail area.
Textbox named: txtDPO with a control source of DO_DEF_OPP (from a query)
Textbox named: txtYield with a control source of 1-[txtDPO]

In the footer, I want to get the product of the Yield textboxes. But I
don't know how to refer to the Yield textboxes in a continuous form. I tried
=Exp(Sum(Log([txtYield]))) because I thought it would loop through all the
txtYield textboxes for all the rows, but it appears not.

Maybe I do have to go to code, but I was hoping to avoid it.

Any other suggestions are appreciated. Maybe I've missed something, or
worst case, what I'm trying to do is impossible.

Thanks again,
Jay

:

the unbound control that contains the expression is not *also* named
txtYield, is it? if it is, change it - you can't use a circular reference in
Access.

i tried the expression you posted in a form, and it worked fine for me. i
tested in an A2000 db running in Acess2003, so that may be why, since you're
using A97. have you tried looking up the Exp() and Log() functions in Help
to see if they're documented in A97 - maybe one or both don't exist in that
version.

if you can't get it working, i suppose you could duplicate the result in VBA
by looping through a recordset and doing the math on each loop, carrying the
result from loop to loop.

hth


Hi all,

I'm trying to calculate the product of a group of textboxes and I keep
getting a #Name? error and I don't understand why. Hopefully someone can
point me in the right direction. I'm using Access 97.

The form is a Continuous Form.
In the Detail area of that form, I have a total of 9 columns (textboxes).
The values of 5 of those columns/textboxes are supplied by the Record
Source
of the form (a query). The remaining 4 values are calculated values,
mostly
division and multiplication of the various textboxes on that row.
In the Footer of that form, I'm doing a variety of calculations. Mostly
summing,
division, multiplication and one product.

To be clear, by product, I mean I want the values of a column multiplied
together (regardless of the number of rows in total) and the value
displayed
in the textbox.

I previously posted a question regarding calculation of Product in Access.
I knew how to do it in Excel (simple, use PRODUCT(x) ) but I didn't know
how
to do it in Access. I was told to use =Exp(Sum(Log([fieldname]))) as a
mathematical equivalent.

However, that value as a control source is causing my #Name? error. When
I
remove that value, all other textboxes work as usual. When I put that
value
in, it causes #Name? errors in all my =Sum Control Source values and
#Error
in any calculated Control Source values in the Footer.

The exact code is as follows:
=Exp(Sum(Log([txtYield]))) where txtYield is the name of the textbox in
the
Detail area of the form that I want to multiply together.

If anyone has any ideas on how to solve this, I would really appreciate
it.
I'm at a complete loss, and I need to find a way to calculate the Product
of
this column, to satisfy the data analysis requirements.

If I've left out anything, please let me know, and I'll do my best to
clear
things up.

Thanks,
Jay

P.S. I'm out of work in 5 min, so I will not be able to respond to any
suggestions/questions until tomorrow.
Click to expand...
Click to expand...
Click to expand...
 
You could also use the Nz function:

=Exp(Sum(Log(Nz([Yield],1))))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jay said:
Solved, sort of.

I rewrote the query to do the calculations in the query and not on the
form.
Once I did that, and added an IIf for Is Null, the Control value works
fine
now.

The value is as follows: =Exp(Sum(Log(IIf([Yield] Is Null,1,[Yield]))))

Thanks for your help though! I might have been limited due to Access 97.

Thanks again,
Jay

Jay said:
Wow, quick reply!

The name of the textbox in the footer is txtOverallYield

And yes, that is the result I'm looking for.

Not sure what else to change. Unless it's a problem with A97. Even when
I
try to simply Sum the textboxes (ignoring the longer expression) I get a
#Name? error. I'm wondering if I can't Sum calculated fields maybe? Not
really sure. I'm trying a few other things to see if I can figure it
out.

Thanks,
Jay

tina said:
what is the name of the control in the footer, which has the expression
=Exp(Sum(Log([txtYield])))
as its' ControlSource? if the control name is txtYield, change it. if
it's
not, then you don't have a circular reference.

as for the expression itself, it does work. as i said, when i tested
it, it
worked fine for me. but wait, let's make sure i understand what you
*expect*
it to do. i tested four records with the following values in one field,
as

1
2
3
4

the expression returned 24. then i added a fifth record with the field
value
of 5, and the expression returned 120. is that the result you're
looking
for?

hth


Hi Tina,

I think I was creating a circular reference, but if I am, I'm not
sure how
to resolve it.

The Exp() and Log() functions are available in Access 97, so I do
believe
it
was this circular reference.

If you got it working (regardless of version) I'd like to know how
you did
it. I'm not sure if there is a way around the circular reference
without
going to VBA.

Here's the situation:

I have the following in the detail area.
Textbox named: txtDPO with a control source of DO_DEF_OPP (from a
query)
Textbox named: txtYield with a control source of 1-[txtDPO]

In the footer, I want to get the product of the Yield textboxes. But
I
don't know how to refer to the Yield textboxes in a continuous form.
I
tried
=Exp(Sum(Log([txtYield]))) because I thought it would loop through
all the
txtYield textboxes for all the rows, but it appears not.

Maybe I do have to go to code, but I was hoping to avoid it.

Any other suggestions are appreciated. Maybe I've missed something,
or
worst case, what I'm trying to do is impossible.

Thanks again,
Jay

:

the unbound control that contains the expression is not *also*
named
txtYield, is it? if it is, change it - you can't use a circular
reference in
Access.

i tried the expression you posted in a form, and it worked fine for
me.
i
tested in an A2000 db running in Acess2003, so that may be why,
since
you're
using A97. have you tried looking up the Exp() and Log() functions
in
Help
to see if they're documented in A97 - maybe one or both don't exist
in
that
version.

if you can't get it working, i suppose you could duplicate the
result in
VBA
by looping through a recordset and doing the math on each loop,
carrying
the
result from loop to loop.

hth


Hi all,

I'm trying to calculate the product of a group of textboxes and I
keep
getting a #Name? error and I don't understand why. Hopefully
someone
can
point me in the right direction. I'm using Access 97.

The form is a Continuous Form.
In the Detail area of that form, I have a total of 9 columns
(textboxes).
The values of 5 of those columns/textboxes are supplied by the
Record
Source
of the form (a query). The remaining 4 values are calculated
values,
mostly
division and multiplication of the various textboxes on that row.
In the Footer of that form, I'm doing a variety of calculations.
Mostly
summing,
division, multiplication and one product.

To be clear, by product, I mean I want the values of a column
multiplied
together (regardless of the number of rows in total) and the
value
displayed
in the textbox.

I previously posted a question regarding calculation of Product
in
Access.
I knew how to do it in Excel (simple, use PRODUCT(x) ) but I
didn't
know
how
to do it in Access. I was told to use
=Exp(Sum(Log([fieldname]))) as
a
mathematical equivalent.

However, that value as a control source is causing my #Name?
error.
When
I
remove that value, all other textboxes work as usual. When I put
that
value
in, it causes #Name? errors in all my =Sum Control Source values
and
#Error
in any calculated Control Source values in the Footer.

The exact code is as follows:
=Exp(Sum(Log([txtYield]))) where txtYield is the name of the
textbox
in
the
Detail area of the form that I want to multiply together.

If anyone has any ideas on how to solve this, I would really
appreciate
it.
I'm at a complete loss, and I need to find a way to calculate the
Product
of
this column, to satisfy the data analysis requirements.

If I've left out anything, please let me know, and I'll do my
best to
clear
things up.

Thanks,
Jay

P.S. I'm out of work in 5 min, so I will not be able to respond
to
any
suggestions/questions until tomorrow.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top