Problems with a Sum controlsource, please help.

[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.

 

[Marshall Barton]

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.

 

[Nordivan]

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.

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.

 

[Marshall Barton]

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.

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.>>

 

[Nordivan]

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.

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.>>

 

[Marshall Barton]

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.

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.>>

 

[Nordivan]

I shows error when i use the formula that i shall use.

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.

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.

 

[Marshall Barton]

I shows error when i use the formula that i shall use.

What error?

Did you try it without using CDbl?

 

[Nordivan]

It shows this: #Error

I have tried whitout CDbl.
What to do?

 

[Marshall Barton]

#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).

 

[Nordivan]

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?

#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.

 

[Marshall Barton]

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?

Sorry, but I am completely out of ideas.

 

[Nordivan]

Thanks anyway for your help.
Have a nice day
Regards
Pontus