calculation in a access 2000 form, i need help.

[kingston via AccessMonster.com]

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

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

[quoted text clipped - 10 lines]

 

[Marshall Barton]

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

A control is an object on a form/report that is used to
display/edit data. For example, a textbox is a control.
Many people confuse the word "control" with the word
"field", but a field is a column in a table/query.

You often use a control to display/edit the value of a
field. In this case, the control is "bound" to the field.

The aggregate functions (Count, Sum, etc) do not recognize
controls. You must use the name of fields in an aggrtegate
function.

 

[Marshall Barton]

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.

That syntax is invalid. I don't understand what
Lägg_Till_Artikel2 is, but if it is a form name, the Sum
function will not understand the expression. If that's the
name of a table, you musr use . not !

There is no need to use the table name unless you have the
same field name in more than one table in the form's record
source query. Assuming that your **field** names are
correct, I think this is all you need:

=Nz(Sum(Pris * Antal), 0)

 

[AccessVandal via AccessMonster.com]

Just to be sure, what is the Form’s RecordSource? See Below.

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

This code will work if the Form’s RecordSource is bound to a table name. e.g.
Table Name is “ Lagg_Till_Artikel2†and Column name is “Pris†and “Antalâ€.

Assuming the Form’s ControlName is “Pris†and “Antalâ€, is code should work.

=Nz(Sum([Pris]*[Antal]),0)

If the Form’s RecordSource is a Query than, this code should work.

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

I did a sample on my own, and the code works.

“Lägg� I wondered whether Access has language problems. I did a search and
it appears that it does on some languages. If so, I suggest you go check it
out.

Nordivan wrote:
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.

 

[Nordivan]

Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

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

[quoted text clipped - 10 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.

 

[Nordivan]

Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:

Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

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

[quoted text clipped - 10 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.

 

[Marshall Barton]

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.
--
Marsh
MVP [MS Access]

Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:

Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

Nordivan wrote:
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...

[quoted text clipped - 10 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.

 

[Nordivan]

Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.
--
Marsh
MVP [MS Access]

Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:

Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

Nordivan wrote:
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...

[quoted text clipped - 10 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.

 

[Marshall Barton]

Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.
--
Marsh
MVP [MS Access]

Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.

Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

 

[Nordivan]

I have a problem here, from the begining i used text as values for the field:
Price, price 2, number an so but later when i changed the field price, price
2 and number to an numeric type i cant get any decimals regardless if i
choose in table how many decimals i want or nor for that specified field.
"Marshall Barton" skrev:

Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.
--
Marsh
MVP [MS Access]

Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.


Nordivan wrote:
Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

 

[Nordivan]

Hi, i recall another thing. Under ech field in the table you can choose
Fieldsize, format and decimals. There i have choosen as following, Fieldsize:
Double, Format: standard and Decials: Automatic. I know this important
to."Marshall Barton" skrev:

Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.
--
Marsh
MVP [MS Access]

Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.


Nordivan wrote:
Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

 

[Marshall Barton]

Double should contain the fractional part of a number. At
first I thought maybe you had the numbers FieldSize set to
Integer or Long, but Double should be ok. Depending on what
these numbers represent and what calculations use these
numbers, it might be better to use the Currency type that
can be more accurate if the numbers only have 3 or 4 decimal
places.

Note that the Format and DecimalPlaces properties are
strictly used in determining how the number is displayed,
they have no effect on the value of a field (or control).
The only point where these settings have an effect is when
you view a table/query in sheet view or when you display a
value in a text box on a form/report.

Did you get the Sum to work? If not, I still think you may
be using a control name where you need to use a field name.
--
Marsh
MVP [MS Access]

Hi, i recall another thing. Under ech field in the table you can choose
Fieldsize, format and decimals. There i have choosen as following, Fieldsize:
Double, Format: standard and Decials: Automatic. I know this important
to."Marshall Barton" skrev:

Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.

Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.


Nordivan wrote:
Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

 

[Nordivan]

Hi, the nubers i have is an price and some of them have 4 deciamls but the
most of them have 2, so then i run on double?
I cant get the sum to work i can sum the field price but when the field
number is in the calculation in some way it dosent work. Strange but i will
try this: "I still think you may
be using a control name where you need to use a field name."
thanks for your help.
"Marshall Barton" skrev:

Double should contain the fractional part of a number. At
first I thought maybe you had the numbers FieldSize set to
Integer or Long, but Double should be ok. Depending on what
these numbers represent and what calculations use these
numbers, it might be better to use the Currency type that
can be more accurate if the numbers only have 3 or 4 decimal
places.

Note that the Format and DecimalPlaces properties are
strictly used in determining how the number is displayed,
they have no effect on the value of a field (or control).
The only point where these settings have an effect is when
you view a table/query in sheet view or when you display a
value in a text box on a form/report.

Did you get the Sum to work? If not, I still think you may
be using a control name where you need to use a field name.
--
Marsh
MVP [MS Access]

Hi, i recall another thing. Under ech field in the table you can choose
Fieldsize, format and decimals. There i have choosen as following, Fieldsize:
Double, Format: standard and Decials: Automatic. I know this important
to."Marshall Barton" skrev:

Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.


Nordivan wrote:
Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.


Nordivan wrote:
Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

 

[Nordivan]

You said that you still think i may
be using a control name where i need to use a field name.
Is this what you mean?
= [table_name]![Price] * [table_name]![Number]


"Nordivan" skrev:

Hi, the nubers i have is an price and some of them have 4 deciamls but the
most of them have 2, so then i run on double?
I cant get the sum to work i can sum the field price but when the field
number is in the calculation in some way it dosent work. Strange but i will
try this: "I still think you may
be using a control name where you need to use a field name."
thanks for your help.
"Marshall Barton" skrev:

Double should contain the fractional part of a number. At
first I thought maybe you had the numbers FieldSize set to
Integer or Long, but Double should be ok. Depending on what
these numbers represent and what calculations use these
numbers, it might be better to use the Currency type that
can be more accurate if the numbers only have 3 or 4 decimal
places.

Note that the Format and DecimalPlaces properties are
strictly used in determining how the number is displayed,
they have no effect on the value of a field (or control).
The only point where these settings have an effect is when
you view a table/query in sheet view or when you display a
value in a text box on a form/report.

Did you get the Sum to work? If not, I still think you may
be using a control name where you need to use a field name.
--
Marsh
MVP [MS Access]

Hi, i recall another thing. Under ech field in the table you can choose
Fieldsize, format and decimals. There i have choosen as following, Fieldsize:
Double, Format: standard and Decials: Automatic. I know this important
to."Marshall Barton" skrev:

Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.


Nordivan wrote:
Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.


Nordivan wrote:
Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:

Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

 

[Marshall Barton]

No, adding the table name shouldn't make any difference. If
you did try that, you would need a dot, not a exclamation.

You need to check the name of the **field** in the form's
recordsource table/query. That is the name you must use in
the Sum function. The reason I am harping on this is that
you still seem to be using the word "field" when it looks
like you are talking about a control on the form, the Sum
function can not use a control name. Another concern I have
is that you sometimes use the name Number and at other times
you use the name Numbers.

Another possibility is if you are using a query as the
record source (please clarify), then the query might be
converting the value to a text string. This is easy to test
by changing the detail section's text box expression to:

=Price * CDbl(Numbers)

and the form's footer section text box expression to:

=Sum(Price * CDbl(Numbers))
--
Marsh
MVP [MS Access]

You said that you still think i may
be using a control name where i need to use a field name.
Is this what you mean?
= [table_name]![Price] * [table_name]![Number]


"Nordivan" skrev:

Hi, the nubers i have is an price and some of them have 4 deciamls but the
most of them have 2, so then i run on double?
I cant get the sum to work i can sum the field price but when the field
number is in the calculation in some way it dosent work. Strange but i will
try this: "I still think you may
be using a control name where you need to use a field name."
thanks for your help.
"Marshall Barton" skrev:

Double should contain the fractional part of a number. At
first I thought maybe you had the numbers FieldSize set to
Integer or Long, but Double should be ok. Depending on what
these numbers represent and what calculations use these
numbers, it might be better to use the Currency type that
can be more accurate if the numbers only have 3 or 4 decimal
places.

Note that the Format and DecimalPlaces properties are
strictly used in determining how the number is displayed,
they have no effect on the value of a field (or control).
The only point where these settings have an effect is when
you view a table/query in sheet view or when you display a
value in a text box on a form/report.

Did you get the Sum to work? If not, I still think you may
be using a control name where you need to use a field name.

Nordivan wrote:
Hi, i recall another thing. Under ech field in the table you can choose
Fieldsize, format and decimals. There i have choosen as following, Fieldsize:
Double, Format: standard and Decials: Automatic. I know this important
to.

"Marshall Barton" skrev:
Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.


Nordivan wrote:
Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.


Nordivan wrote:
Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:
Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

 

[Nordivan]

First of all i made an table, then an query/question where i sorted out which
productgroup i wanted to show and from that query/question i made my form. I
have used the correct names: price is called price in both the table, the
query and the form. Its the same with all the others.
"Marshall Barton" skrev:

No, adding the table name shouldn't make any difference. If
you did try that, you would need a dot, not a exclamation.

You need to check the name of the **field** in the form's
recordsource table/query. That is the name you must use in
the Sum function. The reason I am harping on this is that
you still seem to be using the word "field" when it looks
like you are talking about a control on the form, the Sum
function can not use a control name. Another concern I have
is that you sometimes use the name Number and at other times
you use the name Numbers.

Another possibility is if you are using a query as the
record source (please clarify), then the query might be
converting the value to a text string. This is easy to test
by changing the detail section's text box expression to:

=Price * CDbl(Numbers)

and the form's footer section text box expression to:

=Sum(Price * CDbl(Numbers))
--
Marsh
MVP [MS Access]

You said that you still think i may
be using a control name where i need to use a field name.
Is this what you mean?
= [table_name]![Price] * [table_name]![Number]


"Nordivan" skrev:

Hi, the nubers i have is an price and some of them have 4 deciamls but the
most of them have 2, so then i run on double?
I cant get the sum to work i can sum the field price but when the field
number is in the calculation in some way it dosent work. Strange but i will
try this: "I still think you may
be using a control name where you need to use a field name."
thanks for your help.
"Marshall Barton" skrev:

Double should contain the fractional part of a number. At
first I thought maybe you had the numbers FieldSize set to
Integer or Long, but Double should be ok. Depending on what
these numbers represent and what calculations use these
numbers, it might be better to use the Currency type that
can be more accurate if the numbers only have 3 or 4 decimal
places.

Note that the Format and DecimalPlaces properties are
strictly used in determining how the number is displayed,
they have no effect on the value of a field (or control).
The only point where these settings have an effect is when
you view a table/query in sheet view or when you display a
value in a text box on a form/report.

Did you get the Sum to work? If not, I still think you may
be using a control name where you need to use a field name.

Nordivan wrote:
Hi, i recall another thing. Under ech field in the table you can choose
Fieldsize, format and decimals. There i have choosen as following, Fieldsize:
Double, Format: standard and Decials: Automatic. I know this important
to.

"Marshall Barton" skrev:
Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.


Nordivan wrote:
Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.


Nordivan wrote:
Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:
Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().

 

[Marshall Barton]

Did you try my last thought about using CDbl? If so, what
happened?

The only other explanation I can come up with to explain the
error is if the field name is a reserved word. If changing
the name of the field from Number to something more unusual
doesn't help, then I am at a loss to explain why you are
getting an error.
--
Marsh
MVP [MS Access]

First of all i made an table, then an query/question where i sorted out which
productgroup i wanted to show and from that query/question i made my form. I
have used the correct names: price is called price in both the table, the
query and the form. Its the same with all the others.
"Marshall Barton" skrev:

No, adding the table name shouldn't make any difference. If
you did try that, you would need a dot, not a exclamation.

You need to check the name of the **field** in the form's
recordsource table/query. That is the name you must use in
the Sum function. The reason I am harping on this is that
you still seem to be using the word "field" when it looks
like you are talking about a control on the form, the Sum
function can not use a control name. Another concern I have
is that you sometimes use the name Number and at other times
you use the name Numbers.

Another possibility is if you are using a query as the
record source (please clarify), then the query might be
converting the value to a text string. This is easy to test
by changing the detail section's text box expression to:

=Price * CDbl(Numbers)

and the form's footer section text box expression to:

=Sum(Price * CDbl(Numbers))
--
Marsh
MVP [MS Access]

You said that you still think i may
be using a control name where i need to use a field name.
Is this what you mean?
= [table_name]![Price] * [table_name]![Number]


"Nordivan" skrev:
Hi, the nubers i have is an price and some of them have 4 deciamls but the
most of them have 2, so then i run on double?
I cant get the sum to work i can sum the field price but when the field
number is in the calculation in some way it dosent work. Strange but i will
try this: "I still think you may
be using a control name where you need to use a field name."
thanks for your help.
"Marshall Barton" skrev:

Double should contain the fractional part of a number. At
first I thought maybe you had the numbers FieldSize set to
Integer or Long, but Double should be ok. Depending on what
these numbers represent and what calculations use these
numbers, it might be better to use the Currency type that
can be more accurate if the numbers only have 3 or 4 decimal
places.

Note that the Format and DecimalPlaces properties are
strictly used in determining how the number is displayed,
they have no effect on the value of a field (or control).
The only point where these settings have an effect is when
you view a table/query in sheet view or when you display a
value in a text box on a form/report.

Did you get the Sum to work? If not, I still think you may
be using a control name where you need to use a field name.

Nordivan wrote:
Hi, i recall another thing. Under ech field in the table you can choose
Fieldsize, format and decimals. There i have choosen as following, Fieldsize:
Double, Format: standard and Decials: Automatic. I know this important
to.

"Marshall Barton" skrev:
Did you double check that the Numbers field in the table is
a numeric type? If the field is type Text then that would
cause the error.

Just to clarify the nomenclature, forms and reports do not
contain fields, they contain **controls**. A control can
be used to display the value of a field in the form/report's
RecordSource tabl/query. It is not unusual to use the word
field when referring to a control that displays a field's
value, but it is ambiguous when talking about both a field
and a related control in the same topic.


Nordivan wrote:
Hi, the field is called numbers both in the table and the form.

"Marshall Barton" skrev:

I suspect that the error is because the **field** in the
**table** is not named Numbers.

I haven't seen it in a long time, but I remember that there
was an issue where an error in one aggregate expression,
could cause error in other, valid expressions. Make sure
that all of them are calculating correctly before you add
the one with the problem.


Nordivan wrote:
Hi , i now think i found the fiedl tha causes the problem. I said that i
tried the expression =Sum([Price]) in all the fields that i shall use in my
calculation but the number field is filled with number so i havent tried it
but when i in one of the fileds in form footer try this expression
=Sum([Numbers]) then i get this: #Error so now i kno what causes the problem
now i just need to finsd what in the properties for the field number who
causes it.> use in my calculation

"Nordivan" skrev:
Hi, i have tried this expression =Sum([Price]) in all the fields that i shall
use in my calculation an in every of them it works, but when i try for
example to multiply the field number with the field price as an controlsource
to the field tot price it dosent work. Why?
Every field works one by one with calculations.

"kingston via AccessMonster.com" skrev:
Try the other fields one by one. Which ones work with the function Sum().
Yes, only numeric fields will work, but this may help you find fields that
you think are numeric but really aren't. Once you're sure that two fields
are numeric and both work with Sum(), try multiplying the two fields within
Sum().