This one's a challenge: 1.9 not equaling 1.9..... What's going on???

[pwizzle]

Hi all,

I've got a real challenge this time with an access database that I'm
writing. I'm at my witts end trying to figure out a feasible solution,
but to no avail. I'm writing this database for work, and failing to
come up with a fix for this problem is going to be a major problem.

Long story short:

1) I work for a grocery store, and I have imported a listing of about
12,000 items that we sell into this database. This list includes
dimensional data about every product (height, width, and depth)

2) We have several people out in our stores measuring product in an
effort to audit this data. They input their measurements into a form
that I put together and the form dumps the audit measurements into
separate columns in the same table via a recordset.

3) I have conditional formatting set up that compares the data in the
table to the data they enter into the text boxes. This conditional
formatting works fine and correctly identifies when the two numbers do
not equal by coloring the text box red.

My problem is that I have a query put together that spits out all of
the data in the table. For example, column #1 in the query is titled
"System Case Height" and column #2 is titled "Audited Case Height".
Column #3 is set up to spit out "Yes" or "No" depending on if these
numbers are equal or not.

Here is the code for column 3:

CaseHeight_In_Tollerance?:
IIf([System_Case_Height]-[Audit_Case_Height]=0),"Yes","No")

Most of the time, Yes and no are returned correctly. However once every
few records, the query returns "No" when the numbers are the same.
Looking directly at the query, 1.9 is in column #1 and 1.9 is in column
#2, yet the query still returns "No".

In the table, both columns are set to type "Number" and Decimal places
to "Auto".

The key here (I presume) has something to do with the precision of the
numbers. I'm betting that access is somehow storing an extra decimal
place or something that I cannot see... however I am stumped as to how
to test for this.

This question is very long and very detailed, and I will understand if
I get no responses, but I'm keeping my fingers crossed that maybe
someone knows a way of going about testing this problem out that I
haven't thought of... I will be eternally grateful for any help that
you guys can provide.

--Pat

 

[Conan Kelly]

Pat,

Yeah, that is my guess too. I come across this problem in Excel all the time. When I have my number formats set to 2 decimal
places, sometimes I will get a negative zero! (HUH?!?! WTF?!?! HOW CAN YOU HAVE A -0.00) Well when you run the decimal places out
to 5,6, or 7 places, then the problem is apparent. In your case, 1.9 does not eaqual 1.900004558.

Using your formula, in Excel-ese I would write "=If(ROUND([System_Case_Height],2)-ROUND([Audit_Case_Height],2)=0),"Yes","No")".

I don't know for sure if the ROUND() function is available in Access (you would think it would be--the syntax may be
different--there probably is another function that should be able to accomplish the same thing).

I hope this helps,

Conan Kelly

Hi all,

I've got a real challenge this time with an access database that I'm
writing. I'm at my witts end trying to figure out a feasible solution,
but to no avail. I'm writing this database for work, and failing to
come up with a fix for this problem is going to be a major problem.

Long story short:

1) I work for a grocery store, and I have imported a listing of about
12,000 items that we sell into this database. This list includes
dimensional data about every product (height, width, and depth)

2) We have several people out in our stores measuring product in an
effort to audit this data. They input their measurements into a form
that I put together and the form dumps the audit measurements into
separate columns in the same table via a recordset.

3) I have conditional formatting set up that compares the data in the
table to the data they enter into the text boxes. This conditional
formatting works fine and correctly identifies when the two numbers do
not equal by coloring the text box red.

My problem is that I have a query put together that spits out all of
the data in the table. For example, column #1 in the query is titled
"System Case Height" and column #2 is titled "Audited Case Height".
Column #3 is set up to spit out "Yes" or "No" depending on if these
numbers are equal or not.

Here is the code for column 3:

CaseHeight_In_Tollerance?:
IIf([System_Case_Height]-[Audit_Case_Height]=0),"Yes","No")

Most of the time, Yes and no are returned correctly. However once every
few records, the query returns "No" when the numbers are the same.
Looking directly at the query, 1.9 is in column #1 and 1.9 is in column
#2, yet the query still returns "No".

In the table, both columns are set to type "Number" and Decimal places
to "Auto".

The key here (I presume) has something to do with the precision of the
numbers. I'm betting that access is somehow storing an extra decimal
place or something that I cannot see... however I am stumped as to how
to test for this.

This question is very long and very detailed, and I will understand if
I get no responses, but I'm keeping my fingers crossed that maybe
someone knows a way of going about testing this problem out that I
haven't thought of... I will be eternally grateful for any help that
you guys can provide.

--Pat

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It depends on the data type of your Number. Is it a Double or a Single
or a Decimal (Access 2002 & above)? If possible use a Decimal w/ a
Scale (maximum number of total digits) and Precision (maximum number of
digits to the right of the decimal) that fits your numbers range. E.g.:

Decimal(3,1) would allow numbers in the range 99.9 to 00.0 (3 digits,
total [the Scale], and 1 digit to the right of the decimal point [the
Precision]).

I've always avoided the Single data type 'cuz of its wonky handling of
decimals.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKLl0oechKqOuFEgEQJUEACfXhOslboHKQ4QMBqxgBPZhg68X7AAoNKH
uMcM47cGnzr0BEsgGkSViW67
=ZL6P
-----END PGP SIGNATURE-----

Hi all,

I've got a real challenge this time with an access database that I'm
writing. I'm at my witts end trying to figure out a feasible solution,
but to no avail. I'm writing this database for work, and failing to
come up with a fix for this problem is going to be a major problem.

Long story short:

1) I work for a grocery store, and I have imported a listing of about
12,000 items that we sell into this database. This list includes
dimensional data about every product (height, width, and depth)

2) We have several people out in our stores measuring product in an
effort to audit this data. They input their measurements into a form
that I put together and the form dumps the audit measurements into
separate columns in the same table via a recordset.

3) I have conditional formatting set up that compares the data in the
table to the data they enter into the text boxes. This conditional
formatting works fine and correctly identifies when the two numbers do
not equal by coloring the text box red.

My problem is that I have a query put together that spits out all of
the data in the table. For example, column #1 in the query is titled
"System Case Height" and column #2 is titled "Audited Case Height".
Column #3 is set up to spit out "Yes" or "No" depending on if these
numbers are equal or not.

Here is the code for column 3:

CaseHeight_In_Tollerance?:
IIf([System_Case_Height]-[Audit_Case_Height]=0),"Yes","No")

Most of the time, Yes and no are returned correctly. However once every
few records, the query returns "No" when the numbers are the same.
Looking directly at the query, 1.9 is in column #1 and 1.9 is in column
#2, yet the query still returns "No".

In the table, both columns are set to type "Number" and Decimal places
to "Auto".

The key here (I presume) has something to do with the precision of the
numbers. I'm betting that access is somehow storing an extra decimal
place or something that I cannot see... however I am stumped as to how
to test for this.

This question is very long and very detailed, and I will understand if
I get no responses, but I'm keeping my fingers crossed that maybe
someone knows a way of going about testing this problem out that I
haven't thought of... I will be eternally grateful for any help that
you guys can provide.

 

[Guest]

In the table, both columns are set to type "Number" and Decimal places to
"Auto".

There is one more piece of the puzzle and you hit the nali on the head in
the precision of the numbers.

Open the table in design view and click on the first field. Look in the
grid below where the first line says Field Size. They need to all be the
same. I think Single would do for your data. You might also consider
setting the decimal places to two as I doubt if you need any measurement more
precise than that.

Hi all,

I've got a real challenge this time with an access database that I'm
writing. I'm at my witts end trying to figure out a feasible solution,
but to no avail. I'm writing this database for work, and failing to
come up with a fix for this problem is going to be a major problem.

Long story short:

1) I work for a grocery store, and I have imported a listing of about
12,000 items that we sell into this database. This list includes
dimensional data about every product (height, width, and depth)

2) We have several people out in our stores measuring product in an
effort to audit this data. They input their measurements into a form
that I put together and the form dumps the audit measurements into
separate columns in the same table via a recordset.

3) I have conditional formatting set up that compares the data in the
table to the data they enter into the text boxes. This conditional
formatting works fine and correctly identifies when the two numbers do
not equal by coloring the text box red.

My problem is that I have a query put together that spits out all of
the data in the table. For example, column #1 in the query is titled
"System Case Height" and column #2 is titled "Audited Case Height".
Column #3 is set up to spit out "Yes" or "No" depending on if these
numbers are equal or not.

Here is the code for column 3:

CaseHeight_In_Tollerance?:
IIf([System_Case_Height]-[Audit_Case_Height]=0),"Yes","No")

Most of the time, Yes and no are returned correctly. However once every
few records, the query returns "No" when the numbers are the same.
Looking directly at the query, 1.9 is in column #1 and 1.9 is in column
#2, yet the query still returns "No".

In the table, both columns are set to type "Number" and Decimal places
to "Auto".

The key here (I presume) has something to do with the precision of the
numbers. I'm betting that access is somehow storing an extra decimal
place or something that I cannot see... however I am stumped as to how
to test for this.

This question is very long and very detailed, and I will understand if
I get no responses, but I'm keeping my fingers crossed that maybe
someone knows a way of going about testing this problem out that I
haven't thought of... I will be eternally grateful for any help that
you guys can provide.

--Pat

 

[Marshall Barton]

I've got a real challenge this time with an access database that I'm
writing. I'm at my witts end trying to figure out a feasible solution,
but to no avail. I'm writing this database for work, and failing to
come up with a fix for this problem is going to be a major problem.

Long story short:

1) I work for a grocery store, and I have imported a listing of about
12,000 items that we sell into this database. This list includes
dimensional data about every product (height, width, and depth)

2) We have several people out in our stores measuring product in an
effort to audit this data. They input their measurements into a form
that I put together and the form dumps the audit measurements into
separate columns in the same table via a recordset.

3) I have conditional formatting set up that compares the data in the
table to the data they enter into the text boxes. This conditional
formatting works fine and correctly identifies when the two numbers do
not equal by coloring the text box red.

My problem is that I have a query put together that spits out all of
the data in the table. For example, column #1 in the query is titled
"System Case Height" and column #2 is titled "Audited Case Height".
Column #3 is set up to spit out "Yes" or "No" depending on if these
numbers are equal or not.

Here is the code for column 3:

CaseHeight_In_Tollerance?:
IIf([System_Case_Height]-[Audit_Case_Height]=0),"Yes","No")

[snip]

The standard way of dealing with precision anomalies is to
do the comparison something like this:

IIf(Abs([System_Case_Height] - [Audit_Case_Height]) <
..000001, "Yes","No")

 

[pwizzle]

Thanks everyone for the prompt input to my question, I'm overwhelmed by
the willingness of people to help out on here.

A modified version of Marshall's suggestion worked perfectly, I can't
believe that I hadn't thought of that myself.

Thanks again all!

I've got a real challenge this time with an access database that I'm
writing. I'm at my witts end trying to figure out a feasible solution,
but to no avail. I'm writing this database for work, and failing to
come up with a fix for this problem is going to be a major problem.

Long story short:

1) I work for a grocery store, and I have imported a listing of about
12,000 items that we sell into this database. This list includes
dimensional data about every product (height, width, and depth)

2) We have several people out in our stores measuring product in an
effort to audit this data. They input their measurements into a form
that I put together and the form dumps the audit measurements into
separate columns in the same table via a recordset.

3) I have conditional formatting set up that compares the data in the
table to the data they enter into the text boxes. This conditional
formatting works fine and correctly identifies when the two numbers do
not equal by coloring the text box red.

My problem is that I have a query put together that spits out all of
the data in the table. For example, column #1 in the query is titled
"System Case Height" and column #2 is titled "Audited Case Height".
Column #3 is set up to spit out "Yes" or "No" depending on if these
numbers are equal or not.

Here is the code for column 3:

CaseHeight_In_Tollerance?:
IIf([System_Case_Height]-[Audit_Case_Height]=0),"Yes","No")

[snip]

The standard way of dealing with precision anomalies is to
do the comparison something like this:

IIf(Abs([System_Case_Height] - [Audit_Case_Height]) <
.000001, "Yes","No")