crazy results from X-int(X)

[John Sh]

1.1-int(1.1) returns results from 0.1 to 0.10000000000023 to any other apparently random number into a type "double" field, "remveg.accession number".
I have set the decimal points to 1 and still get the same result.
I have set the data type to "single" and this seems to consistently give the required answer but when I put this into an update query I get a "Data type mismatch in criteria expression" error, with or without the inverted commas..

UPDATE RemVeg SET RemVeg.Specimen = "A"
WHERE (([RemVeg]![Accession Number]="0.1"));

Using the "X-int(X)" directly in the query only updates those records where the result is exactly "0.1", 3 out of 1500.
I have exported the table to Excel and get the same results using the formula "=if(a2-int(a2)=0.1, "A","")"
Only those records, cells, where the result is "0.1" are updated.

 

[The_Doc_Man]

OK, we are looking at issues of presentation vs. computation on a binary machine attempting a decimal fractional computation.

First, the expression 1.1-int(1.1) will probably NOT come out even. The semantics of your expression are:

(constant) 1.1 minus (re-floated) (integerized) 1.1

This happens because VBA can't leave INT function results as integers in this context. The first part of the expression isn't an integer so that integer has to get promoted - back to 1.0 so as to avoid mixed-mode arithmetic. Therefore your result is 1.1 - 1.0 which gives you 0.1... or does it?

The answer is NO, it doesn't, because you are dealing with a binary representation of a fraction. Now 0.1 (in real-world math) is just 1/2 * 1/5 = 1/10, right? Well, 1/2 in binary is pretty easy. But 1/5 is decidedly NOT easy, because 1/5 is NOT a power of 1/2.

In fact, the binary representation of 1/10 is just 0.00011001100110011001100....00110011 as a repeating decimal. BUT a double only supports something like 53 bits of fraction after which you have to truncate the fraction. Now you have a truncated fraction which varies from the original value by something in the 54th bit.

If you use a Format property in a table or query, the number in the field remains as 0.000110011... but its PRESENTATION will be truncated to 0.1 if you display it and it will look OK. But if you do math on the fraction in that field (and comparison IS doing math), you have the problem that the comparison sees the internal numbers and yet you provided a quoted string.

The solution to your problem is probably something like this:

UPDATE RemVeg SET RemVeg.Specimen = "A"
WHERE Trim$(Format( [RemVeg]![Accession Number], "###.#"))="0.1");

which converts the number from internal/computational format to text-oriented/presentation format and THEN does the comparison. There are other ways to do this, but this is probably the most direct approach. You could also look up the ROUND function as a way to try to make this work, but again you are dealing with a fraction that isn't an integer power of 1/2.

 

[arnelgp]

you are comparing it to String "0.1" on your Query:

UPDATE RemVeg SET RemVeg.Specimen = "A"
WHERE (([RemVeg]![Accession Number]="0.1"));


you should probably use Round() function:

UPDATE RemVeg SET RemVeg.Specimen = "A"
WHERE Round([RemVeg]![Accession Number], 1) = 0.1;

 

[John Sh]

OK, we are looking at issues of presentation vs. computation on a binary machine attempting a decimal fractional computation.

First, the expression 1.1-int(1.1) will probably NOT come out even. The semantics of your expression are:

(constant) 1.1 minus (re-floated) (integerized) 1.1

Thanks Doc_man.
I knew it would be convoluted. I will give your system a try.

 

[John Sh]

you are comparing it to String "0.1" on your Query:

Thank you.
Access actually wraps the .01 in commas if you hit enter after entering the criteria.
The error I was getting was related to the "Single" type. I will try rounding as well as Doc_Man's method.

 

[John Sh]

Turns out the simplest solution was to change the "Accession Number" field to a string and then use "right(string,1) to extract the "decimal".
So:
UPDATE RemVeg SET RemVeg.Specimen = "A"
WHERE (right([RemVeg]![Accession Number],1)="1"));
and then change it back to a double.

 

[cheekybuddha]

Why use a double (with its inherent rounding errors)?

Do you need more than 4 decimal places? If not, use Currency datatype

Turns out the simplest solution was to change the "Accession Number" field to a Currency and then use:

UPDATE RemVeg SET RemVeg.Specimen = "A"
WHERE [Accession Number] - Int([Accession Number]) = 0.1;

 

[John Sh]

Why use a double (with its inherent rounding errors)?

Do you need more than 4 decimal places? If not, use Currency datatype

Turns out the simplest solution was to change the "Accession Number" field to a Currency and then use:

UPDATE RemVeg SET RemVeg.Specimen = "A"
WHERE [Accession Number] - Int([Accession Number]) = 0.1;

I use a double because I need the decimal place, but ony one decimal place so currency would not be suitable. So the simplest solution was as I said, convert to text!

 

[cheekybuddha]

so currency would not be suitable

Why not?

 

[Minty]

Agree with @cheekybuddha where you need simple decimals always use the currency data type in access or Decimal (18,4) in SQL server.

It avoids all the rounding/floating point inaccuracy issues you are seeing.
You just need to format it as a general number in your forms.