using the sum of a query column in a form

[Guest]

I have a Query which works well, the interesting part of which is a column
containing the remaining inventory of each item in stock. The query is called
"INV TOTAL" and the column is called "Expr1". Then I created a form with a
text field the "control source" of the field is "=Sum([INV TOTAL]![Expr1]).
Instead of seeing the total number of all items in stock, i.e. the total of
the "Expr1" column, I get "#Error". Any ideas?

 

[Marshall Barton]

I have a Query which works well, the interesting part of which is a column
containing the remaining inventory of each item in stock. The query is called
"INV TOTAL" and the column is called "Expr1". Then I created a form with a
text field the "control source" of the field is "=Sum([INV TOTAL]![Expr1]).
Instead of seeing the total number of all items in stock, i.e. the total of
the "Expr1" column, I get "#Error". Any ideas?

At least the ! should be a .

But there should be no need for the table name here:

=Sum(Expr1)

 

[Laminator Guy]

"Laminator Guy" <Laminator (e-mail address removed)>
wrote:

I have a Query which works well, the interesting part of which is a column
containing the remaining inventory of each item in stock. The query is called
"INV TOTAL" and the column is called "Expr1". Then I created a form with a
text field the "control source" of the field is "=Sum([INV TOTAL]![Expr1]).
Instead of seeing the total number of all items in stock, i.e. the total of
the "Expr1" column, I get "#Error". Any ideas?

At least the ! should be a .

But there should be no need for the table name here:

=Sum(Expr1)

The "Expression Builder" put the "!" in there for me. Changing it to a "." gets
me: "#Error". Changing it to "=Sum(Expr1)" gets me: "#Error". Actually, when I
change the property to =Sum(Expr1) and hit enter, it changes it to
=Sum([Expr1]), but I guess that's the same thing.

Does anyone besides me get annoyed at this "#Error" crap? How do you figure out
what the darned problem is if you don't have a better error message than "#Error"?

stv

 

[Marshall Barton]

I have a Query which works well, the interesting part of which is a column
containing the remaining inventory of each item in stock. The query is called
"INV TOTAL" and the column is called "Expr1". Then I created a form with a
text field the "control source" of the field is "=Sum([INV TOTAL]![Expr1]).
Instead of seeing the total number of all items in stock, i.e. the total of
the "Expr1" column, I get "#Error". Any ideas?

At least the ! should be a .

But there should be no need for the table name here:

=Sum(Expr1)

The "Expression Builder" put the "!" in there for me. Changing it to a "." gets
me: "#Error". Changing it to "=Sum(Expr1)" gets me: "#Error". Actually, when I
change the property to =Sum(Expr1) and hit enter, it changes it to
=Sum([Expr1]), but I guess that's the same thing.

Does anyone besides me get annoyed at this "#Error" crap? How do you figure out
what the darned problem is if you don't have a better error message than "#Error"?

The expression builder? Well, I guess that's a clue, but I
can't guess at what that might imply. It probaly means that
the text box is also named Expr1. This could cause an
#Error in an ordinary expression, but I don't think it will
with an aggregate function. It might also mean that there
is something funny about the query field (but you said the
query works fine by itself, so that's probably not it).

One other thing to check is if there is another text box
with an aggregate function that also gets #Error. I have
seen cases where a problem in one aggregate can cause a
problem in others.

Where is this text box? It should be in the form's header
or footer section (not the page header/footer).

Sorry, I just can't think of any other straws to grasp at
here.

 

[Laminator Guy]

The expression builder? Well, I guess that's a clue, but I
can't guess at what that might imply.

I'm not sure what you mean here. If you don't know what the Expression Builder
is, the little Clippy help guy can tell you all about it.

It probaly means that
the text box is also named Expr1.

The text box is named "text0".

One other thing to check is if there is another text box
with an aggregate function that also gets #Error. I have
seen cases where a problem in one aggregate can cause a
problem in others.

No, there are no other "#Error" messages anywhere.

Where is this text box? It should be in the form's header
or footer section (not the page header/footer).

This is in the "Detail" section. What difference does that make? I'm using
Access 2000, how about you? I double-clicked on "Create a form in Design view",
and put a text field into it. I went to the "properties" of the text field, and
clicked on the three dots ("...") next to "Data>>Control Source" to get to
the Expression Builder. I double-clicked on "Queries", then the Query and the
column I wanted SUMmed. I change the word "expr" to "SUM", and then I expected
to be able to hit "F5" and see the sum of that column.

When I see "#Error", that really doesn't give me any starting point to trying
to figure out what I'm doing wrong. This is a real deficiency of Access 2000,
speaking as someone who has programmed in a lot of different computer languages.

Sorry, I just can't think of any other straws to grasp at
here.

Thanks for trying. Is there anyone else out there who has any ideas?

 

[Guest]

Your form is looking at the query isn't it? Bring up the "Properties" for the
form and set the "Record Source" by hitting the drop down arrow and selecting
the desired query then click on your control on the form to get the
"Properties" for the control. Now click the drop down arrow on the "Control
Source" property and select the column from your query. At this point switch
to form view and see what you have. If this looks good, switch back to design
view and modify your expression with the "=Sum()"

Keith Walker