On the fly Subtraction

[Emma]

I am trying to subtract a calculation on the fly so it updates as the user
enters more information. Here's what I have but I'm getting a Name? error

=[Loans - Table].[AmountofLoan]-(Sum([AmountOfPayment]))

 

[Fred]

Couple things to check

- Don't know if "Loans - Table" is literally in there, if so that
syntax/naming might cause problems.
- Make sure that none of those names are also the names of the boxes, i.e.
that 1. You renamed any boxes to something other than the field names when
you used the field names in expressions. 2. You didn't try to use a box
name (result of a calculation) in an expression.

 

[Emma]

Hi Fred,

Loans - Table is an actual table that exists. I don't know what you mean by
"box" do you mean field? Because AmountofPayment is a field.

Couple things to check

- Don't know if "Loans - Table" is literally in there, if so that
syntax/naming might cause problems.
- Make sure that none of those names are also the names of the boxes, i.e.
that 1. You renamed any boxes to something other than the field names when
you used the field names in expressions. 2. You didn't try to use a box
name (result of a calculation) in an expression.

I am trying to subtract a calculation on the fly so it updates as the user
enters more information. Here's what I have but I'm getting a Name? error

=[Loans - Table].[AmountofLoan]-(Sum([AmountOfPayment]))

 

[Fred]

Hello Emma,


What I meant was assuming that your control with this problem on your form
(or report) is a text box. When you just drop in one of these from the
field list, the name of the box is the field name, and the field name is also
the control source. If you modify the control source to being an
expression, you have to rename the box a different name than the field name.
If you forget, (as I have many times) you'll get the error message that you
describe.

The second possibility I don't know the detailed rules well enough to
describe, particularly for forms (vs. reports). But in reports, you
sometimes get such a message by trying to use the result of one calculation
as an input to a different calculation, and this is done by using the name of
the first control in the second calculaiton.

Hi Fred,

Loans - Table is an actual table that exists. I don't know what you mean by
"box" do you mean field? Because AmountofPayment is a field.

Couple things to check

- Don't know if "Loans - Table" is literally in there, if so that
syntax/naming might cause problems.
- Make sure that none of those names are also the names of the boxes, i.e.
that 1. You renamed any boxes to something other than the field names when
you used the field names in expressions. 2. You didn't try to use a box
name (result of a calculation) in an expression.

I am trying to subtract a calculation on the fly so it updates as the user
enters more information. Here's what I have but I'm getting a Name? error

=[Loans - Table].[AmountofLoan]-(Sum([AmountOfPayment]))

 

[Emma]

I'm not sure how to name the first control as I tried the following

Total: Sum([AmountOfPayment]) and I tried
Total = Sum([AmountOfPayment]) neither of which produced any results

Hello Emma,


What I meant was assuming that your control with this problem on your form
(or report) is a text box. When you just drop in one of these from the
field list, the name of the box is the field name, and the field name is also
the control source. If you modify the control source to being an
expression, you have to rename the box a different name than the field name.
If you forget, (as I have many times) you'll get the error message that you
describe.

The second possibility I don't know the detailed rules well enough to
describe, particularly for forms (vs. reports). But in reports, you
sometimes get such a message by trying to use the result of one calculation
as an input to a different calculation, and this is done by using the name of
the first control in the second calculaiton.

Hi Fred,

Loans - Table is an actual table that exists. I don't know what you mean by
"box" do you mean field? Because AmountofPayment is a field.

Couple things to check

- Don't know if "Loans - Table" is literally in there, if so that
syntax/naming might cause problems.
- Make sure that none of those names are also the names of the boxes, i.e.
that 1. You renamed any boxes to something other than the field names when
you used the field names in expressions. 2. You didn't try to use a box
name (result of a calculation) in an expression.







:

I am trying to subtract a calculation on the fly so it updates as the user
enters more information. Here's what I have but I'm getting a Name? error

=[Loans - Table].[AmountofLoan]-(Sum([AmountOfPayment]))

 

[Fred]

Hello Emma,

First, am I right in assuming that you are doing this on a FORM? If yes,
then what I said is applicable, and here's more info on that.

It looks like you may not know the difference between a "Control Sours"
Property and a "Name" property (??)


The most common type of item on a form is a "text box" . If you look up the
properties of the text box, the first two properties on the list are:

"Name"
"Control Source"

BTW, in form design view, unless you dig into the properties of a box,
you'll only see the "Control Source" property.

So lets say you just created a text box on your form by dragging
"AmountOfPayment" onto it from your field list.

These two properties of the text box will be as follows:

Name: AmountOfPayment
Countrol Source: AmountOfPayment


So, now you make a mistake and just edit the control source to:

= Sum([AmountOfPayment])

Now those properties will be:

Name: AmountOfPayment
Countrol Source: = Sum([AmountOfPayment])

This will return the error message that you describe.

The solution is to change the name property to something different. Like
"AmountOfPayment2" or "XYZ"

 

[Emma]

Fred, sorry to upset you. but maybe I need to explain it a little better.
AmountOfLoan is in the main form (Testing Loans Subform) and AmountOfPayment
is in the subdatasheet on the form (Loans - Payment History) which holds
dates payments are made, amounts and a running total. I would like a Amount
owing to be anywhere either on the subform or the form?

 

[Emma]

Ok Fred,

I'm an idiot, it turns out it's easier to put the total due in the first
form and reference the subform instead of the other way around. So it's
working perfectly now, thanks for your help. I guess I just needed you to
talk me through it.