Storing and using Decimals

[Guest]

In order to have accuracy when using decimals I was advised to use the
currency type in a table. However, when I try to use the information I am
getting incorrect results.
Example: In the table I have a field "Tolerance" in which is stored .50 but
when I use a variable to use the data in a calculation (want to multiply by
..50), the variable shows "
0.05".
I have the table field "Tolerance" set for currency the fields show what I
enter with a $ and to decimal places "$0.05".
I have the variable "SinglePackTolerance" as follows: Dim
SinglePackTolerance As Double which should be 0.50 but is returned as "0.05"

I have other fields that seem to be working correctly.
they have the following data MasterWeight 0015.40 is picked up as 15.4,
EmptyWeight 000.62 picked up as 0.62, PackWeight 000.74 picked up as 0.74.
The difference I see is that the $0.50 entry has only 0 to the left of the
decimal and just one whole number to the right of the decimal.

Is currency the right choose for what I am trying to do?

Do I need to format the table field to something like 000.00 in order to
remove the dollar sign?

How do I avoid picking up incorrect data?

I have tried changing Dim SinglePackTolerance As to Single and Integer but
the results are still not correct.

Thank you in advance for your help.

Allan

 

[Guest]

If you want the precission, try using a data type of Double. That should
give you better accuracy. The exact formatting of output is best done as
part of displaying the data.

 

[Guest]

DevalilaJohn
I am using Double in the declared variable. Do you mean I should use Double
on the table field rather than currency?

Allan

 

[Brendan Reynolds]

0.5 should not be changing to 0.05 regardless of what data type you use. I
suspect the problem is in the code or SQL you're using to retrieve the value
rather than in the data type you're using to store it.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Yes, just use double. Unless you are concerned about loss of signifigance
more than eight or nine places to the right of the decimal point, you will
have your greatest accuracy this way. Also by using all doubles, you
eliminate the possibility of loss of signifigance when converting between
data types.

 

[Guest]

Brendan
This is the code I am using in the After Update event of a form
"SixDifinPackageForm"

Dim SinglePackTolerance As Double

SinglePackTolerance = Nz(DLookup("[PackWeightTolerance]", "JobsPackaging",
"[JobNumber]=" & Me.UsedWithJob & "And [PackagingType]=" &
Me.PackagingTypeID))

"PackWeightTolerance' is the field - currency with $0.50 as the data in the
"JobsPackaging" form.

"UsedWithJob" and "PackagingTypeID" are text boxes on the form
By using a MsgBox to display the result I get 0.05.

Allan

 

[Guest]

DevalilaJohn
In order to change the table field I believe I will need to shut down the
workstations. I have a database MDE front end and MDB back end on the network.

What procedure should I follow to make this change?

Will I have problems changing the field from currency to double?

Thanks
Allan

 

[Brendan Reynolds]

I can't reproduce that. With the same code, I get 0.5. Could you post the
code you're using to display the value in the message box?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan
This is the code I am using in the After Update event of a form
"SixDifinPackageForm"

Dim SinglePackTolerance As Double

SinglePackTolerance = Nz(DLookup("[PackWeightTolerance]", "JobsPackaging",
"[JobNumber]=" & Me.UsedWithJob & "And [PackagingType]=" &
Me.PackagingTypeID))

"PackWeightTolerance' is the field - currency with $0.50 as the data in
the
"JobsPackaging" form.

"UsedWithJob" and "PackagingTypeID" are text boxes on the form
By using a MsgBox to display the result I get 0.05.

Allan

0.5 should not be changing to 0.05 regardless of what data type you use.
I
suspect the problem is in the code or SQL you're using to retrieve the
value
rather than in the data type you're using to store it.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

If you're running in that type of networked environment, I hope that you have
a separate development mode to work in.

Changing the data type should be no problem, when you do the save, it will
be accomplished. As you are moving from a more restrictive data type to a
less restrictive one, there shouldn't be issues.

As to whether or not you will need to shut down the workstations, I'd test
it. If they are used to getting the results of the calculation from the back
end, and the result is a double you should have no problem. If they are
expecting a currency value, you may need to update the .mde accordingly. I
don't know how a currency data type is stored, but would suspect that it is
in some type of integer.

Depending on how many workstations you have and how far apart they are, you
could do anything from going around to each to make any changes, to shipping
update software, etc. It's hard to say more without knowing a lot more about
your environment.

 

[Guest]

Brendan
After the DLookup I am using the following. I also am getting the wrong
result when I use the "SinglePackTolerance" as the multiplier so I know it is
what the MsgBox is showing.
MsgBox "" & SinglePackTolerance & ""

Allan

I can't reproduce that. With the same code, I get 0.5. Could you post the
code you're using to display the value in the message box?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan
This is the code I am using in the After Update event of a form
"SixDifinPackageForm"

Dim SinglePackTolerance As Double

SinglePackTolerance = Nz(DLookup("[PackWeightTolerance]", "JobsPackaging",
"[JobNumber]=" & Me.UsedWithJob & "And [PackagingType]=" &
Me.PackagingTypeID))

"PackWeightTolerance' is the field - currency with $0.50 as the data in
the
"JobsPackaging" form.

"UsedWithJob" and "PackagingTypeID" are text boxes on the form
By using a MsgBox to display the result I get 0.05.

Allan

0.5 should not be changing to 0.05 regardless of what data type you use.
I
suspect the problem is in the code or SQL you're using to retrieve the
value
rather than in the data type you're using to store it.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


DevalilaJohn
I am using Double in the declared variable. Do you mean I should use
Double
on the table field rather than currency?

Allan

:

If you want the precission, try using a data type of Double. That
should
give you better accuracy. The exact formatting of output is best done
as
part of displaying the data.

:

In order to have accuracy when using decimals I was advised to use
the
currency type in a table. However, when I try to use the information
I
am
getting incorrect results.
Example: In the table I have a field "Tolerance" in which is stored
.50
but
when I use a variable to use the data in a calculation (want to
multiply by
.50), the variable shows "
0.05".
I have the table field "Tolerance" set for currency the fields show
what I
enter with a $ and to decimal places "$0.05".
I have the variable "SinglePackTolerance" as follows: Dim
SinglePackTolerance As Double which should be 0.50 but is returned
as
"0.05"

I have other fields that seem to be working correctly.
they have the following data MasterWeight 0015.40 is picked up as
15.4,
EmptyWeight 000.62 picked up as 0.62, PackWeight 000.74 picked up as
0.74.
The difference I see is that the $0.50 entry has only 0 to the left
of
the
decimal and just one whole number to the right of the decimal.

Is currency the right choose for what I am trying to do?

Do I need to format the table field to something like 000.00 in
order
to
remove the dollar sign?

How do I avoid picking up incorrect data?

I have tried changing Dim SinglePackTolerance As to Single and
Integer
but
the results are still not correct.

Thank you in advance for your help.

Allan

 

[Guest]

DevalilaJohn
I did a successful transition to Double and it is working. I noticed that
even when I restricted the field to 2 decimal places I am getting records
that show 3.
Thank you for your help.

 

[Brendan Reynolds]

Well, I am at a loss. I used the information you provided about your table
structure and form, but simply could not reproduce the problem. It appears
from one of your other posts in this thread, though, that the issue is now
resolved to your satisfaction, so I guess all's well that ends well!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan
After the DLookup I am using the following. I also am getting the wrong
result when I use the "SinglePackTolerance" as the multiplier so I know it
is
what the MsgBox is showing.
MsgBox "" & SinglePackTolerance & ""

Allan

I can't reproduce that. With the same code, I get 0.5. Could you post the
code you're using to display the value in the message box?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Brendan
This is the code I am using in the After Update event of a form
"SixDifinPackageForm"

Dim SinglePackTolerance As Double

SinglePackTolerance = Nz(DLookup("[PackWeightTolerance]",
"JobsPackaging",
"[JobNumber]=" & Me.UsedWithJob & "And [PackagingType]=" &
Me.PackagingTypeID))

"PackWeightTolerance' is the field - currency with $0.50 as the data in
the
"JobsPackaging" form.

"UsedWithJob" and "PackagingTypeID" are text boxes on the form
By using a MsgBox to display the result I get 0.05.

Allan

:

0.5 should not be changing to 0.05 regardless of what data type you
use.
I
suspect the problem is in the code or SQL you're using to retrieve the
value
rather than in the data type you're using to store it.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


DevalilaJohn
I am using Double in the declared variable. Do you mean I should use
Double
on the table field rather than currency?

Allan

:

If you want the precission, try using a data type of Double. That
should
give you better accuracy. The exact formatting of output is best
done
as
part of displaying the data.

:

In order to have accuracy when using decimals I was advised to
use
the
currency type in a table. However, when I try to use the
information
I
am
getting incorrect results.
Example: In the table I have a field "Tolerance" in which is
stored
.50
but
when I use a variable to use the data in a calculation (want to
multiply by
.50), the variable shows "
0.05".
I have the table field "Tolerance" set for currency the fields
show
what I
enter with a $ and to decimal places "$0.05".
I have the variable "SinglePackTolerance" as follows: Dim
SinglePackTolerance As Double which should be 0.50 but is
returned
as
"0.05"

I have other fields that seem to be working correctly.
they have the following data MasterWeight 0015.40 is picked up as
15.4,
EmptyWeight 000.62 picked up as 0.62, PackWeight 000.74 picked up
as
0.74.
The difference I see is that the $0.50 entry has only 0 to the
left
of
the
decimal and just one whole number to the right of the decimal.

Is currency the right choose for what I am trying to do?

Do I need to format the table field to something like 000.00 in
order
to
remove the dollar sign?

How do I avoid picking up incorrect data?

I have tried changing Dim SinglePackTolerance As to Single and
Integer
but
the results are still not correct.

Thank you in advance for your help.

Allan