calculation in a access 2000 form, i need help.

[Guest]

Hi, im doing an article register in access 2000 and all the articles are
bound to diffrent product categories (Every product categories is an bigger
product with many other articles in it) I have a main table with all the
articles in and then i have done a question where the question asks which
product categorie i will show. From that question i have done a form where i
will have to write in which product categorie i will show when i open it. In
that form i have the following fields: article number, product name, product
type, supplier, Price, old price , Total price and the number of how many of
an certain article who is used in this product. What i´ve done so far is that
is done a calculation where i multiply the price with the number of how many
of an certain article who is used in this product to get the total price for
every article because in this product you need more than one of some
articles. In the formfoot i want to make a calculation so i cant get the
total sum of all the 71 articles total price but it dosent work i only get
this #Fault.
The expreccion i write in is: =Sum([Text21]).
Where Text21 is the text box where the total price for every article ends.
Look att this picture to see how my form looks like (The tex in this form is
in swedish)

http://server3.pictiger.com/img/635805/computers-and-electronic-gadgets/formul-r.jpg

 

[kingston via AccessMonster.com]

Try setting the control source to: =Sum("[Text21]")

Hi, im doing an article register in access 2000 and all the articles are
bound to diffrent product categories (Every product categories is an bigger
product with many other articles in it) I have a main table with all the
articles in and then i have done a question where the question asks which
product categorie i will show. From that question i have done a form where i
will have to write in which product categorie i will show when i open it. In
that form i have the following fields: article number, product name, product
type, supplier, Price, old price , Total price and the number of how many of
an certain article who is used in this product. What i´ve done so far is that
is done a calculation where i multiply the price with the number of how many
of an certain article who is used in this product to get the total price for
every article because in this product you need more than one of some
articles. In the formfoot i want to make a calculation so i cant get the
total sum of all the 71 articles total price but it dosent work i only get
this #Fault.
The expreccion i write in is: =Sum([Text21]).
Where Text21 is the text box where the total price for every article ends.
Look att this picture to see how my form looks like (The tex in this form is
in swedish)

http://server3.pictiger.com/img/635805/computers-and-electronic-gadgets/formul-r.jpg

 

[Guest]

Hi, thanks for your answer but i already have tried this and it dosent work
either, any other ideas. I really think i writing the correct expression to
calculate the Sum of this articles total prize but something seams to be
wrong. I can also tell you that if i use the same expression: =Sum([Text21])
but changes Text21 to the field Price then i get the calculation done. So i
can see the total price of all the articles if there are only one of them in
this product but some of the articles are needed more than one in this
product and thats why i want to have the Sum of the fiels Total price and not
only the Sum of field price.
Hope that someone have any solution on this problem.


"kingston via AccessMonster.com" skrev:

Try setting the control source to: =Sum("[Text21]")

Hi, im doing an article register in access 2000 and all the articles are
bound to diffrent product categories (Every product categories is an bigger
product with many other articles in it) I have a main table with all the
articles in and then i have done a question where the question asks which
product categorie i will show. From that question i have done a form where i
will have to write in which product categorie i will show when i open it. In
that form i have the following fields: article number, product name, product
type, supplier, Price, old price , Total price and the number of how many of
an certain article who is used in this product. What i´ve done so far is that
is done a calculation where i multiply the price with the number of how many
of an certain article who is used in this product to get the total price for
every article because in this product you need more than one of some
articles. In the formfoot i want to make a calculation so i cant get the
total sum of all the 71 articles total price but it dosent work i only get
this #Fault.
The expreccion i write in is: =Sum([Text21]).
Where Text21 is the text box where the total price for every article ends.
Look att this picture to see how my form looks like (The tex in this form is
in swedish)

http://server3.pictiger.com/img/635805/computers-and-electronic-gadgets/formul-r.jpg

 

[kingston via AccessMonster.com]

How are you populating the form? Is there an underlying query that performs
the calculations or are you performing calculations in the form itself? If
you perform the total price calculations in the form, this expression will
not work because the form doesn't know that it must do the record-level
calculations and then provide the results to the form footer control. Use a
query to populate the total price. Otherwise, you need to perform a
calculation on the original data set using the function DSum().

Hi, thanks for your answer but i already have tried this and it dosent work
either, any other ideas. I really think i writing the correct expression to
calculate the Sum of this articles total prize but something seams to be
wrong. I can also tell you that if i use the same expression: =Sum([Text21])
but changes Text21 to the field Price then i get the calculation done. So i
can see the total price of all the articles if there are only one of them in
this product but some of the articles are needed more than one in this
product and thats why i want to have the Sum of the fiels Total price and not
only the Sum of field price.
Hope that someone have any solution on this problem.

"kingston via AccessMonster.com" skrev:

Try setting the control source to: =Sum("[Text21]")

[quoted text clipped - 19 lines]

 

[Guest]

Hi, im doing all the calculations in the form. First of all i have an
calculation where i calculate the total price of each article because you
need more than one of some articles to this product, =[Price]*[Number:] and
this calculation works fine and gives me an total price for each article in
the field Tot price. And heres where the problem comes in, the Calculation:
=[Price]*[Number:] is the control source to the field Tot price which is the
field that i in the foot want to get the sum of with the expression:
=Sum([Tot price]). I can in the same field in the footer get the sum of the
filed price but i want to get the sum of the field tot price which have been
calculated and this is not possible you say. I have also tried todo some
expression direct in the question that form in build on but that didnt work.

 

[kingston via AccessMonster.com]

In the form footer, use =Sum([Price]*[Number]).

Hi, im doing all the calculations in the form. First of all i have an
calculation where i calculate the total price of each article because you
need more than one of some articles to this product, =[Price]*[Number:] and
this calculation works fine and gives me an total price for each article in
the field Tot price. And heres where the problem comes in, the Calculation:
=[Price]*[Number:] is the control source to the field Tot price which is the
field that i in the foot want to get the sum of with the expression:
=Sum([Tot price]). I can in the same field in the footer get the sum of the
filed price but i want to get the sum of the field tot price which have been
calculated and this is not possible you say. I have also tried todo some
expression direct in the question that form in build on but that didnt work.

 

[Guest]

Hi, tahnaks for your help. I have tried to write =Sum([Price]*[Number]) in
the form footer and the only thing i get in the text square is this: #Error.
So is there anything els i can do?

"kingston via AccessMonster.com" skrev:

In the form footer, use =Sum([Price]*[Number]).

Hi, im doing all the calculations in the form. First of all i have an
calculation where i calculate the total price of each article because you
need more than one of some articles to this product, =[Price]*[Number:] and
this calculation works fine and gives me an total price for each article in
the field Tot price. And heres where the problem comes in, the Calculation:
=[Price]*[Number:] is the control source to the field Tot price which is the
field that i in the foot want to get the sum of with the expression:
=Sum([Tot price]). I can in the same field in the footer get the sum of the
filed price but i want to get the sum of the field tot price which have been
calculated and this is not possible you say. I have also tried todo some
expression direct in the question that form in build on but that didnt work.

 

[AccessVandal via AccessMonster.com]

Hi Nordivan,

Have you tried using the expression builder?

Try some thing like,

Nz(Sum([TableName]![ColumnName] * [TableName]![ColumnName]),0)

At the control property control source, click the button “…â€, look at the
folder called Tables, open it and look for your table and the column and
click on it.

Give it a shot.

Hi, tahnaks for your help. I have tried to write =Sum([Price]*[Number]) in
the form footer and the only thing i get in the text square is this: #Error.
So is there anything els i can do?

 

[Marshall Barton]

From what you said earlier, that should be:

=Sum([Price]*[Number:])

It is a good idea to use only alphanumeric characters in
names.

Note that the aggegate functions only operate on **fields**
in the form/report record source table/query. They are
unaware of ** controls** in the form/report.
--
Marsh
MVP [MS Access]

Hi, tahnaks for your help. I have tried to write =Sum([Price]*[Number]) in
the form footer and the only thing i get in the text square is this: #Error.
So is there anything els i can do?

"kingston via AccessMonster.com" skrev:

In the form footer, use =Sum([Price]*[Number]).

Hi, im doing all the calculations in the form. First of all i have an
calculation where i calculate the total price of each article because you
need more than one of some articles to this product, =[Price]*[Number:] and
this calculation works fine and gives me an total price for each article in
the field Tot price. And heres where the problem comes in, the Calculation:
=[Price]*[Number:] is the control source to the field Tot price which is the
field that i in the foot want to get the sum of with the expression:
=Sum([Tot price]). I can in the same field in the footer get the sum of the
filed price but i want to get the sum of the field tot price which have been
calculated and this is not possible you say. I have also tried todo some
expression direct in the question that form in build on but that didnt work.

 

[kingston via AccessMonster.com]

I'm sorry but sometimes it's easier to start over. Fortunately, your form
doesn't appear to be too complicated. You may have a bad piece of code or a
conflict with a control name that's creating a problem. Start by allowing
the form wizard to create a form for you. Then add a text control to the
form footer with the formula =Sum([Price]*[Number]). Naturally, use the
correct control names. Then check the form and add to it piece by piece.
HTH

Hi, tahnaks for your help. I have tried to write =Sum([Price]*[Number]) in
the form footer and the only thing i get in the text square is this: #Error.
So is there anything els i can do?

"kingston via AccessMonster.com" skrev:

In the form footer, use =Sum([Price]*[Number]).

[quoted text clipped - 9 lines]

 

[Guest]

I tried what you said but it gives me an error.
But thanks for the help.

"AccessVandal via AccessMonster.com" skrev:

Hi Nordivan,

Have you tried using the expression builder?

Try some thing like,

Nz(Sum([TableName]![ColumnName] * [TableName]![ColumnName]),0)

At the control property control source, click the button “…â€, look at the
folder called Tables, open it and look for your table and the column and
click on it.

Give it a shot.

Hi, tahnaks for your help. I have tried to write =Sum([Price]*[Number]) in
the form footer and the only thing i get in the text square is this: #Error.
So is there anything els i can do?

 

[Guest]

=Sum([Price]*[Number:]) is what i´ve tried from the start so it´s not working.
What do you mean with this:
Note that the aggegate functions only operate on **fields**
in the form/report record source table/query. They are
unaware of ** controls** in the form/report.
Thanks for your help.

"Marshall Barton" skrev:

From what you said earlier, that should be:

=Sum([Price]*[Number:])

It is a good idea to use only alphanumeric characters in
names.

Note that the aggegate functions only operate on **fields**
in the form/report record source table/query. They are
unaware of ** controls** in the form/report.
--
Marsh
MVP [MS Access]

Hi, tahnaks for your help. I have tried to write =Sum([Price]*[Number]) in
the form footer and the only thing i get in the text square is this: #Error.
So is there anything els i can do?

"kingston via AccessMonster.com" skrev:

In the form footer, use =Sum([Price]*[Number]).

Nordivan wrote:
Hi, im doing all the calculations in the form. First of all i have an
calculation where i calculate the total price of each article because you
need more than one of some articles to this product, =[Price]*[Number:] and
this calculation works fine and gives me an total price for each article in
the field Tot price. And heres where the problem comes in, the Calculation:
=[Price]*[Number:] is the control source to the field Tot price which is the
field that i in the foot want to get the sum of with the expression:
=Sum([Tot price]). I can in the same field in the footer get the sum of the
filed price but i want to get the sum of the field tot price which have been
calculated and this is not possible you say. I have also tried todo some
expression direct in the question that form in build on but that didnt work.

 

[Guest]

Hi, okey i think i already tried that but i will try again.
Thanks for your help anyway.

"kingston via AccessMonster.com" skrev:

I'm sorry but sometimes it's easier to start over. Fortunately, your form
doesn't appear to be too complicated. You may have a bad piece of code or a
conflict with a control name that's creating a problem. Start by allowing
the form wizard to create a form for you. Then add a text control to the
form footer with the formula =Sum([Price]*[Number]). Naturally, use the
correct control names. Then check the form and add to it piece by piece.
HTH

Hi, tahnaks for your help. I have tried to write =Sum([Price]*[Number]) in
the form footer and the only thing i get in the text square is this: #Error.
So is there anything els i can do?

"kingston via AccessMonster.com" skrev:

In the form footer, use =Sum([Price]*[Number]).

[quoted text clipped - 9 lines]

calculated and this is not possible you say. I have also tried todo some
expression direct in the question that form in build on but that didnt work.

 

[Guest]

Hi again, i now tried to delet the form and make a new one but when i try to
run the code in the new forms it dosent work either. What am im doing wrong
is there any of the fieldnames that are incorrect?
I dont understand what im doing wrong.

"kingston via AccessMonster.com" skrev:

I'm sorry but sometimes it's easier to start over. Fortunately, your form
doesn't appear to be too complicated. You may have a bad piece of code or a
conflict with a control name that's creating a problem. Start by allowing
the form wizard to create a form for you. Then add a text control to the
form footer with the formula =Sum([Price]*[Number]). Naturally, use the
correct control names. Then check the form and add to it piece by piece.
HTH

Hi, tahnaks for your help. I have tried to write =Sum([Price]*[Number]) in
the form footer and the only thing i get in the text square is this: #Error.
So is there anything els i can do?

"kingston via AccessMonster.com" skrev:

In the form footer, use =Sum([Price]*[Number]).

[quoted text clipped - 9 lines]

calculated and this is not possible you say. I have also tried todo some
expression direct in the question that form in build on but that didnt work.

 

[AccessVandal via AccessMonster.com]

I hope you didn’t copy and paste the code into the controlsource. It will
never work without editing.

Just to be sure you understood this.

[TableName] is the name of your table, e.g. “Productsâ€, “Articlesâ€,
“Contactsâ€
[ColumnName] is the column name e.g “ItemIDâ€, “ArticleIDâ€, “ProductIDâ€, etc.

It appears that using wizard to create a form may not be the correct choice
for you.

First, the name of the Control, in this case “Numberâ€.

“Number†is a reserve name in access.
You may have put square brackets around it in your code but your Form control
is still name “Numberâ€. This is not good.

Try to rename your control to “txtNumber†and see if you can resolve the
problem.

So, your code should look like,

= Nz(Sum([txtPrice]*[txtNumber]),0)

or

= Nz(Sum([Article]![Price]*[Article]![Number]),0)

 

[Guest]

I dindt copy it in , i did exacly as you said. Cause i done dose things
before but i only get an error: The syntax of the expression in not guilty.
When have this expression
=Nz(Sum([Lägg_Till_Artikel2]![Pris]*[Lägg_Till_Artikel2]![Antal]),0) as the
controlsource.

"AccessVandal via AccessMonster.com" skrev:

I hope you didn’t copy and paste the code into the controlsource. It will
never work without editing.

Just to be sure you understood this.

[TableName] is the name of your table, e.g. “Productsâ€, “Articlesâ€,
“Contactsâ€
[ColumnName] is the column name e.g “ItemIDâ€, “ArticleIDâ€, “ProductIDâ€, etc.

It appears that using wizard to create a form may not be the correct choice
for you.

First, the name of the Control, in this case “Numberâ€.

“Number†is a reserve name in access.
You may have put square brackets around it in your code but your Form control
is still name “Numberâ€. This is not good.

Try to rename your control to “txtNumber†and see if you can resolve the
problem.

So, your code should look like,

= Nz(Sum([txtPrice]*[txtNumber]),0)

or

= Nz(Sum([Article]![Price]*[Article]![Number]),0)

Nordivan wrote:
I tried what you said but it gives me an error.
But thanks for the help.

 

[kingston via AccessMonster.com]

Go into any VB module and try to compile your program (Debug menu). I think
that you have a problem there; you may have created a function or variable
with a reserved name. You said you already tried to recreate the form. What
happened exactly? Don't attach any code attached to the form yet; just
create it from the form wizard (use a different name for the form just to
test things) and add the one calculated control in the form footer. If this
does not work, the problem is elsewhere. BTW, make backups of your file.

Hi again, i now tried to delet the form and make a new one but when i try to
run the code in the new forms it dosent work either. What am im doing wrong
is there any of the fieldnames that are incorrect?
I dont understand what im doing wrong.

"kingston via AccessMonster.com" skrev:

I'm sorry but sometimes it's easier to start over. Fortunately, your form
doesn't appear to be too complicated. You may have a bad piece of code or a

[quoted text clipped - 15 lines]

 

[kingston via AccessMonster.com]

Also, try Compact and Repair Database...

Hi again, i now tried to delet the form and make a new one but when i try to
run the code in the new forms it dosent work either. What am im doing wrong
is there any of the fieldnames that are incorrect?
I dont understand what im doing wrong.

"kingston via AccessMonster.com" skrev:

I'm sorry but sometimes it's easier to start over. Fortunately, your form
doesn't appear to be too complicated. You may have a bad piece of code or a

[quoted text clipped - 15 lines]

 

[Douglas J. Steele]

I would think you'd want to reverse the order of Nz and Sum: you want to sum
0 if either of the fields is Null:

=Sum(Nz([Lägg_Till_Artikel2]![Pris]*[Lägg_Till_Artikel2]![Antal],0))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I dindt copy it in , i did exacly as you said. Cause i done dose things
before but i only get an error: The syntax of the expression in not
guilty.
When have this expression
=Nz(Sum([Lägg_Till_Artikel2]![Pris]*[Lägg_Till_Artikel2]![Antal]),0) as
the
controlsource.

"AccessVandal via AccessMonster.com" skrev:

I hope you didn't copy and paste the code into the controlsource. It will
never work without editing.

Just to be sure you understood this.

[TableName] is the name of your table, e.g. "Products", "Articles",
"Contacts"
[ColumnName] is the column name e.g "ItemID", "ArticleID", "ProductID",
etc.

It appears that using wizard to create a form may not be the correct
choice
for you.

First, the name of the Control, in this case "Number".

"Number" is a reserve name in access.
You may have put square brackets around it in your code but your Form
control
is still name "Number". This is not good.

Try to rename your control to "txtNumber" and see if you can resolve the
problem.

So, your code should look like,

= Nz(Sum([txtPrice]*[txtNumber]),0)

or

= Nz(Sum([Article]![Price]*[Article]![Number]),0)

Nordivan wrote:
I tried what you said but it gives me an error.
But thanks for the help.

 

[Guest]

If i make a new form and just add this expression =Sum([Price]) it works but
if i add anything more, it will stop working.

"kingston via AccessMonster.com" skrev:

Also, try Compact and Repair Database...

Hi again, i now tried to delet the form and make a new one but when i try to
run the code in the new forms it dosent work either. What am im doing wrong
is there any of the fieldnames that are incorrect?
I dont understand what im doing wrong.

"kingston via AccessMonster.com" skrev:

I'm sorry but sometimes it's easier to start over. Fortunately, your form
doesn't appear to be too complicated. You may have a bad piece of code or a

[quoted text clipped - 15 lines]

calculated and this is not possible you say. I have also tried todo some
expression direct in the question that form in build on but that didnt work.