Same sql in 2 different places on form give different answers.

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have this sql in one place
=[Peso lordoKG]-[PesoDellaCassaVuotaGr]/1000*[NColli]-[PesoPedana] and this
next to it,
=[Peso lordoKG2]-[PesoDellaCassaVuotaGr2]/1000*[NColli2]-[PesoPedana2]
if you put the same numbers into both I get different results. The problem
is in the division by 1000. the weights are in kilos so this is done to
correct the amounts. The second example refuses to divide by 1000 so my
answer is always minus 1000 more than it should be.
The form for the second sql is a copy of the first.
thank you
Michael
 
Are any of these text boxes based on calculations? Try setting their Format
property to General Number or Currency, to indicate how Access should
understand the enties.

You could also add brackets to indicate the order of operation, e.g.:
=[Peso lordoKG2] - ([PesoDellaCassaVuotaGr2] / (1000 * [NColli2])) -
[PesoPedana2]
or did you mean:
=[Peso lordoKG2] - (([PesoDellaCassaVuotaGr2] / 1000) * [NColli2]) -
[PesoPedana2]

For more information about the first issue see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
 
Thank you Allen,
I also tried the following. The strange thing is in the table as well as
the form, I copied and pasted everything and just added the number 2 behind
the field name. Normally for the PesoDellaCassaVuotaGr2 the value would be
800 and this gives me a -750,08
if I change it to 8, i get the correct answer of 41,92. I tried to change
the format on all the numbers and nothing changes.
thanks for you help
Michael
the equation is

Peso lordoKG2
-
PesoDellaCassaVuotaGr2 /1000
*
NColli2
-
PesoPedana2

=[Peso lordoKG2]-([PesoDellaCassaVuotaGr2]/1000)*[NColli2]-[PesoPedana2]
=[Peso lordoKG2]-(([PesoDellaCassaVuotaGr2]/1000)*[NColli2])-[PesoPedana2]

Allen Browne said:
Are any of these text boxes based on calculations? Try setting their
Format property to General Number or Currency, to indicate how Access
should understand the enties.

You could also add brackets to indicate the order of operation, e.g.:
=[Peso lordoKG2] - ([PesoDellaCassaVuotaGr2] / (1000 * [NColli2])) -
[PesoPedana2]
or did you mean:
=[Peso lordoKG2] - (([PesoDellaCassaVuotaGr2] / 1000) * [NColli2]) -
[PesoPedana2]

For more information about the first issue see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael said:
I have this sql in one place
=[Peso lordoKG]-[PesoDellaCassaVuotaGr]/1000*[NColli]-[PesoPedana] and
this next to it,
=[Peso lordoKG2]-[PesoDellaCassaVuotaGr2]/1000*[NColli2]-[PesoPedana2]
if you put the same numbers into both I get different results. The
problem is in the division by 1000. the weights are in kilos so this is
done to correct the amounts. The second example refuses to divide by 1000
so my answer is always minus 1000 more than it should be.
The form for the second sql is a copy of the first.
thank you
Michael
Click to expand...
Click to expand...
 
As far as the fields, they are all combo dropdown list and I check them all
to make sure and they are all standard or general number.
michael

Michael said:
Thank you Allen,
I also tried the following. The strange thing is in the table as well as
the form, I copied and pasted everything and just added the number 2
behind the field name. Normally for the PesoDellaCassaVuotaGr2 the value
would be 800 and this gives me a -750,08
if I change it to 8, i get the correct answer of 41,92. I tried to change
the format on all the numbers and nothing changes.
thanks for you help
Michael
the equation is

Peso lordoKG2
-
PesoDellaCassaVuotaGr2 /1000
*
NColli2
-
PesoPedana2

=[Peso lordoKG2]-([PesoDellaCassaVuotaGr2]/1000)*[NColli2]-[PesoPedana2]
=[Peso lordoKG2]-(([PesoDellaCassaVuotaGr2]/1000)*[NColli2])-[PesoPedana2]

Allen Browne said:
Are any of these text boxes based on calculations? Try setting their
Format property to General Number or Currency, to indicate how Access
should understand the enties.

You could also add brackets to indicate the order of operation, e.g.:
=[Peso lordoKG2] - ([PesoDellaCassaVuotaGr2] / (1000 * [NColli2])) -
[PesoPedana2]
or did you mean:
=[Peso lordoKG2] - (([PesoDellaCassaVuotaGr2] / 1000) * [NColli2]) -
[PesoPedana2]

For more information about the first issue see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael said:
I have this sql in one place
=[Peso lordoKG]-[PesoDellaCassaVuotaGr]/1000*[NColli]-[PesoPedana] and
this next to it,
=[Peso lordoKG2]-[PesoDellaCassaVuotaGr2]/1000*[NColli2]-[PesoPedana2]
if you put the same numbers into both I get different results. The
problem is in the division by 1000. the weights are in kilos so this is
done to correct the amounts. The second example refuses to divide by
1000 so my answer is always minus 1000 more than it should be.
The form for the second sql is a copy of the first.
thank you
Michael
Click to expand...
Click to expand...
Click to expand...
 
Okay, Access is clearly understanding the data types here.

Try breaking it down so that you can pin-point the source of the issue, and
typecasting the data. For example, try:
=CDbl([NColli2]) * (CDbl([PesoDellaCassaVuotaGr2]) / 1000)

If that works, you can then change it to:
=CDbl([NColli2]) * (CDbl([PesoDellaCassaVuotaGr2]) / 1000)
+ CDbl([Peso lordoKG2]) - CDbl([PesoPedana2])

BTW, make sure you have unchecked the boxes under:
Tools | Options | General | Name AutoCorrect.
Explanation of why:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael said:
Thank you Allen,
I also tried the following. The strange thing is in the table as well as
the form, I copied and pasted everything and just added the number 2
behind the field name. Normally for the PesoDellaCassaVuotaGr2 the value
would be 800 and this gives me a -750,08
if I change it to 8, i get the correct answer of 41,92. I tried to change
the format on all the numbers and nothing changes.
thanks for you help
Michael
the equation is

Peso lordoKG2
-
PesoDellaCassaVuotaGr2 /1000
*
NColli2
-
PesoPedana2

=[Peso lordoKG2]-([PesoDellaCassaVuotaGr2]/1000)*[NColli2]-[PesoPedana2]
=[Peso lordoKG2]-(([PesoDellaCassaVuotaGr2]/1000)*[NColli2])-[PesoPedana2]

Allen Browne said:
Are any of these text boxes based on calculations? Try setting their
Format property to General Number or Currency, to indicate how Access
should understand the enties.

You could also add brackets to indicate the order of operation, e.g.:
=[Peso lordoKG2] - ([PesoDellaCassaVuotaGr2] / (1000 * [NColli2])) -
[PesoPedana2]
or did you mean:
=[Peso lordoKG2] - (([PesoDellaCassaVuotaGr2] / 1000) * [NColli2]) -
[PesoPedana2]

For more information about the first issue see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Michael said:
I have this sql in one place
=[Peso lordoKG]-[PesoDellaCassaVuotaGr]/1000*[NColli]-[PesoPedana] and
this next to it,
=[Peso lordoKG2]-[PesoDellaCassaVuotaGr2]/1000*[NColli2]-[PesoPedana2]
if you put the same numbers into both I get different results. The
problem is in the division by 1000. the weights are in kilos so this is
done to correct the amounts. The second example refuses to divide by
1000 so my answer is always minus 1000 more than it should be.
The form for the second sql is a copy of the first.
thank you
Michael
Click to expand...
Click to expand...
Click to expand...
 
Thank you for you help Allen, It must have been a problem in the form, maybe
turning off that autocorrect and remaking the form was the answer as I just
deleted the form and recreated it the same way I made it before and now it
works, it may have been that autocorrect.
Thank you again
michael

Allen Browne said:
Okay, Access is clearly understanding the data types here.

Try breaking it down so that you can pin-point the source of the issue,
and typecasting the data. For example, try:
=CDbl([NColli2]) * (CDbl([PesoDellaCassaVuotaGr2]) / 1000)

If that works, you can then change it to:
=CDbl([NColli2]) * (CDbl([PesoDellaCassaVuotaGr2]) / 1000)
+ CDbl([Peso lordoKG2]) - CDbl([PesoPedana2])

BTW, make sure you have unchecked the boxes under:
Tools | Options | General | Name AutoCorrect.
Explanation of why:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael said:
Thank you Allen,
I also tried the following. The strange thing is in the table as well as
the form, I copied and pasted everything and just added the number 2
behind the field name. Normally for the PesoDellaCassaVuotaGr2 the value
would be 800 and this gives me a -750,08
if I change it to 8, i get the correct answer of 41,92. I tried to change
the format on all the numbers and nothing changes.
thanks for you help
Michael
the equation is

Peso lordoKG2
-
PesoDellaCassaVuotaGr2 /1000
*
NColli2
-
PesoPedana2

=[Peso lordoKG2]-([PesoDellaCassaVuotaGr2]/1000)*[NColli2]-[PesoPedana2]
=[Peso
lordoKG2]-(([PesoDellaCassaVuotaGr2]/1000)*[NColli2])-[PesoPedana2]

Allen Browne said:
Are any of these text boxes based on calculations? Try setting their
Format property to General Number or Currency, to indicate how Access
should understand the enties.

You could also add brackets to indicate the order of operation, e.g.:
=[Peso lordoKG2] - ([PesoDellaCassaVuotaGr2] / (1000 * [NColli2])) -
[PesoPedana2]
or did you mean:
=[Peso lordoKG2] - (([PesoDellaCassaVuotaGr2] / 1000) * [NColli2]) -
[PesoPedana2]

For more information about the first issue see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

I have this sql in one place
=[Peso lordoKG]-[PesoDellaCassaVuotaGr]/1000*[NColli]-[PesoPedana] and
this next to it,
=[Peso lordoKG2]-[PesoDellaCassaVuotaGr2]/1000*[NColli2]-[PesoPedana2]
if you put the same numbers into both I get different results. The
problem is in the division by 1000. the weights are in kilos so this is
done to correct the amounts. The second example refuses to divide by
1000 so my answer is always minus 1000 more than it should be.
The form for the second sql is a copy of the first.
thank you
Michael
Click to expand...
Click to expand...
Click to expand...
 
Back
Top