Problems with a Sum controlsource, please help.

  • Thread starter Thread starter Nordivan
  • Start date Start date
N

Nordivan

I have copied over an article records ( i have made in access) from a
database to a new one and made some small changes but still retain the same
forms. In these forms, I have the following box / text boxes Art No., Name,
Type, Manufacturer, Price (Format currency) , Number (Format Number) & Tot
Price.
Total price is the article price * number (of articles in this product)
At the bottom of the form, I have a box where i want to sum the tot price
and summarizing the articles that I copied from the old database, but do I
enter a new article or change the number on any of the existing articles the
price Tot price do not chane in the sum box.
I'm using the formula: = Summa ([Tot Pris]) in english

In the old database i used the formula = Sum ([Price] * KonvDbl ([Number]))
but this is not working here and = Sum ([Tot Price]) either as i said.
Whats wrong?

Thanks for fast answer.
 
Nordivan said:
I have copied over an article records ( i have made in access) from a
database to a new one and made some small changes but still retain the same
forms. In these forms, I have the following box / text boxes Art No., Name,
Type, Manufacturer, Price (Format currency) , Number (Format Number) & Tot
Price.
Total price is the article price * number (of articles in this product)
At the bottom of the form, I have a box where i want to sum the tot price
and summarizing the articles that I copied from the old database, but do I
enter a new article or change the number on any of the existing articles the
price Tot price do not chane in the sum box.
I'm using the formula: = Summa ([Tot Pris]) in english

In the old database i used the formula = Sum ([Price] * KonvDbl ([Number]))
but this is not working here and = Sum ([Tot Price]) either as i said.
Click to expand...


The aggregate functions (Count, Sum, Avg, etc) only work
with *fields* in the form/report record source table/query.
They do NOT work with *controls* (text box, etc) in the
form/report.

The names of your controls may or may not be the same as the
names of the fields, so make sure you are using the field
names.
 
Thanks but the formula = Sum ([Price] * KonvDbl ([Number])) worked on the
same forms in my old database, there i also wrote them as an controlsource in
a the same text box as know.

Marshall Barton said:
Nordivan said:
I have copied over an article records ( i have made in access) from a
database to a new one and made some small changes but still retain the same
forms. In these forms, I have the following box / text boxes Art No., Name,
Type, Manufacturer, Price (Format currency) , Number (Format Number) & Tot
Price.
Total price is the article price * number (of articles in this product)
At the bottom of the form, I have a box where i want to sum the tot price
and summarizing the articles that I copied from the old database, but do I
enter a new article or change the number on any of the existing articles the
price Tot price do not chane in the sum box.
I'm using the formula: = Summa ([Tot Pris]) in english

In the old database i used the formula = Sum ([Price] * KonvDbl ([Number]))
but this is not working here and = Sum ([Tot Price]) either as i said.
Click to expand...


The aggregate functions (Count, Sum, Avg, etc) only work
with *fields* in the form/report record source table/query.
They do NOT work with *controls* (text box, etc) in the
form/report.

The names of your controls may or may not be the same as the
names of the fields, so make sure you are using the field
names.
Click to expand...
 
I was trying to explain that using Sum([Tot Price]) will
never work and you should not allow it to confuse the issue.

If Price and Number are fields in the form's record source,
then
=Sum([Price] * KonvDbl([Number]))
should work (but I don't know what KonvDbl is doing, maybe
it's your name for CDbl?)

You said something about it working when the form first
opens, but not when you edit a price or number value. I
don't remember ever seeing that kind of problem, but IF
everything else is correct, maybe you can force the
recalculation by using:
Me.[Tot Pris].Requery
in both the Price and Number text box's AfterUpdate event
procedures.

If it worked ok in your old database and it doesn't work in
the new database, then I suspect that some difference
between your old and new databases is causing the problem.
--
Marsh
MVP [MS Access]

Thanks but the formula = Sum ([Price] * KonvDbl ([Number])) worked on the
same forms in my old database, there i also wrote them as an controlsource in
a the same text box as know.

Marshall Barton said:
Nordivan said:
I have copied over an article records ( i have made in access) from a
database to a new one and made some small changes but still retain the same
forms. In these forms, I have the following box / text boxes Art No., Name,
Type, Manufacturer, Price (Format currency) , Number (Format Number) & Tot
Price.
Total price is the article price * number (of articles in this product)
At the bottom of the form, I have a box where i want to sum the tot price
and summarizing the articles that I copied from the old database, but do I
enter a new article or change the number on any of the existing articles the
price Tot price do not chane in the sum box.
I'm using the formula: = Summa ([Tot Pris]) in english

In the old database i used the formula = Sum ([Price] * KonvDbl ([Number]))
but this is not working here and = Sum ([Tot Price]) either as i said.
Click to expand...


The aggregate functions (Count, Sum, Avg, etc) only work
with *fields* in the form/report record source table/query.
They do NOT work with *controls* (text box, etc) in the
form/report.

The names of your controls may or may not be the same as the
names of the fields, so make sure you are using the field
names.>>
Click to expand...
Click to expand...
 
Sorry fo writing =Sum([Tot Price]) i know i dosent work and havent used it
before.
I usedthe formula: =Summa([Pris] * KonvDbl([Antal]))
in english: =Sum([Price] * CDbl([Number]))
and it worked but know it says fault.

Marshall Barton said:
I was trying to explain that using Sum([Tot Price]) will
never work and you should not allow it to confuse the issue.

If Price and Number are fields in the form's record source,
then
=Sum([Price] * KonvDbl([Number]))
should work (but I don't know what KonvDbl is doing, maybe
it's your name for CDbl?)

You said something about it working when the form first
opens, but not when you edit a price or number value. I
don't remember ever seeing that kind of problem, but IF
everything else is correct, maybe you can force the
recalculation by using:
Me.[Tot Pris].Requery
in both the Price and Number text box's AfterUpdate event
procedures.

If it worked ok in your old database and it doesn't work in
the new database, then I suspect that some difference
between your old and new databases is causing the problem.
--
Marsh
MVP [MS Access]

Thanks but the formula = Sum ([Price] * KonvDbl ([Number])) worked on the
same forms in my old database, there i also wrote them as an controlsource in
a the same text box as know.

Marshall Barton said:
Nordivan wrote:

I have copied over an article records ( i have made in access) from a
database to a new one and made some small changes but still retain the same
forms. In these forms, I have the following box / text boxes Art No., Name,
Type, Manufacturer, Price (Format currency) , Number (Format Number) & Tot
Price.
Total price is the article price * number (of articles in this product)
At the bottom of the form, I have a box where i want to sum the tot price
and summarizing the articles that I copied from the old database, but do I
enter a new article or change the number on any of the existing articles the
price Tot price do not chane in the sum box.
I'm using the formula: = Summa ([Tot Pris]) in english

In the old database i used the formula = Sum ([Price] * KonvDbl ([Number]))
but this is not working here and = Sum ([Tot Price]) either as i said.


The aggregate functions (Count, Sum, Avg, etc) only work
with *fields* in the form/report record source table/query.
They do NOT work with *controls* (text box, etc) in the
form/report.

The names of your controls may or may not be the same as the
names of the fields, so make sure you are using the field
names.>>
Click to expand...
Click to expand...
Click to expand...
 
Can you translate the "fault" for me? Does the text box
display #Error or #Name? The details about the "fault"
might mean something that could explain the problem.

I don't see why you would need to use CDbl, try removing it
and see if it makes a difference. (Note that you will get
#Error if there is a record with Null in the Number field)
--
Marsh
MVP [MS Access]

Sorry fo writing =Sum([Tot Price]) i know i dosent work and havent used it
before.
I usedthe formula: =Summa([Pris] * KonvDbl([Antal]))
in english: =Sum([Price] * CDbl([Number]))
and it worked but know it says fault.

Marshall Barton said:
I was trying to explain that using Sum([Tot Price]) will
never work and you should not allow it to confuse the issue.

If Price and Number are fields in the form's record source,
then
=Sum([Price] * KonvDbl([Number]))
should work (but I don't know what KonvDbl is doing, maybe
it's your name for CDbl?)

You said something about it working when the form first
opens, but not when you edit a price or number value. I
don't remember ever seeing that kind of problem, but IF
everything else is correct, maybe you can force the
recalculation by using:
Me.[Tot Pris].Requery
in both the Price and Number text box's AfterUpdate event
procedures.

If it worked ok in your old database and it doesn't work in
the new database, then I suspect that some difference
between your old and new databases is causing the problem.
--
Marsh
MVP [MS Access]

Thanks but the formula = Sum ([Price] * KonvDbl ([Number])) worked on the
same forms in my old database, there i also wrote them as an controlsource in
a the same text box as know.

:

Nordivan wrote:

I have copied over an article records ( i have made in access) from a
database to a new one and made some small changes but still retain the same
forms. In these forms, I have the following box / text boxes Art No., Name,
Type, Manufacturer, Price (Format currency) , Number (Format Number) & Tot
Price.
Total price is the article price * number (of articles in this product)
At the bottom of the form, I have a box where i want to sum the tot price
and summarizing the articles that I copied from the old database, but do I
enter a new article or change the number on any of the existing articles the
price Tot price do not chane in the sum box.
I'm using the formula: = Summa ([Tot Pris]) in english

In the old database i used the formula = Sum ([Price] * KonvDbl ([Number]))
but this is not working here and = Sum ([Tot Price]) either as i said.


The aggregate functions (Count, Sum, Avg, etc) only work
with *fields* in the form/report record source table/query.
They do NOT work with *controls* (text box, etc) in the
form/report.

The names of your controls may or may not be the same as the
names of the fields, so make sure you are using the field
names.>>
Click to expand...
Click to expand...
Click to expand...
 
I shows error when i use the formula that i shall use.

Nordivan said:
Thanks but the formula = Sum ([Price] * KonvDbl ([Number])) worked on the
same forms in my old database, there i also wrote them as an controlsource in
a the same text box as know.

Marshall Barton said:
Nordivan said:
I have copied over an article records ( i have made in access) from a
database to a new one and made some small changes but still retain the same
forms. In these forms, I have the following box / text boxes Art No., Name,
Type, Manufacturer, Price (Format currency) , Number (Format Number) & Tot
Price.
Total price is the article price * number (of articles in this product)
At the bottom of the form, I have a box where i want to sum the tot price
and summarizing the articles that I copied from the old database, but do I
enter a new article or change the number on any of the existing articles the
price Tot price do not chane in the sum box.
I'm using the formula: = Summa ([Tot Pris]) in english

In the old database i used the formula = Sum ([Price] * KonvDbl ([Number]))
but this is not working here and = Sum ([Tot Price]) either as i said.
Click to expand...


The aggregate functions (Count, Sum, Avg, etc) only work
with *fields* in the form/report record source table/query.
They do NOT work with *controls* (text box, etc) in the
form/report.

The names of your controls may or may not be the same as the
names of the fields, so make sure you are using the field
names.
Click to expand...
Click to expand...
 
#Error means that there is something wrong with the values
in Price or Number fields. Maybe the value in the
edited/new record has been changed to Text or the value in
CDbl is Null.

The only other reason I have ever seen for #Error is when a
different text box with another aggregate functiom (Count,
Sum, Avg, etc) also has some kind of error.

I have no other ideas beyond that so you will probably have
to do some debugging to track down the specific issue (e.g.
check if Sum(Price) and Sum(Number) work or not).
 
Now i tried a different thing. I opened the old database and copied one of
the working forms but with a new name. I deleted the info in it and paste in
info from the form in the new database that is not working. Before i pasted
in the new info into the copied form it shows the Sum correct but when i
delete the old info and put in new it says #Error. I cant understand why its
causing this problem when im doing nothing wrong. Any ideas?

Marshall Barton said:
#Error means that there is something wrong with the values
in Price or Number fields. Maybe the value in the
edited/new record has been changed to Text or the value in
CDbl is Null.

The only other reason I have ever seen for #Error is when a
different text box with another aggregate functiom (Count,
Sum, Avg, etc) also has some kind of error.

I have no other ideas beyond that so you will probably have
to do some debugging to track down the specific issue (e.g.
check if Sum(Price) and Sum(Number) work or not).
--
Marsh
MVP [MS Access]

It shows this: #Error

I have tried whitout CDbl.
Click to expand...
Click to expand...
 
Nordivan said:
Now i tried a different thing. I opened the old database and copied one of
the working forms but with a new name. I deleted the info in it and paste in
info from the form in the new database that is not working. Before i pasted
in the new info into the copied form it shows the Sum correct but when i
delete the old info and put in new it says #Error. I cant understand why its
causing this problem when im doing nothing wrong. Any ideas?
Click to expand...


Sorry, but I am completely out of ideas.
 
Back
Top