Displaying 100 % as 1.00 and not just 1 in access table

[ahoneycutt]

I have a number field set to double(reached is the field name). It has a range of numbers going from 0.85 to 1.02, basically 85% to 102%. When the 100% is entered it displays only as a 1.



In a query I have a field(attainment) that gets calculated and displays a double number that looks like 1.076 (107%) or 1.003 (100%) for example.

I have a cacluated field that simply looks for the attainment field and compares it to the reached field and where the value is = to each other it displaces a True or False. When it gets to a number that is 1.001 and it compares it to the field that has only the 1 in it I get a false when it should be a true. All other numbers in the qry work perfect but the ones that are exactly 1.00 fail.

How do I get access to see that 1 is 1.00? I have changed the format in a number of ways and can not get it to work correctly.

Please any advise would be greatful. Have deadline today and need asap.

Thanks,

[JamesDeckert]

It's hard to get two real numbers to be equal. You'll either need to round to a certain number of digits, or convert to integer (which is the more sure way if it'll work for you). Changing the format doesn't change the underlying number, only how it is being displayed. Are you formatting the number to a string variable and then checking two string variables? Maybe you should provide a code snippet of how you're trying to accomplish it.

[ahoneycutt]

I am only running an if statement that reads:

iif([attainment]>=[reached],"True","False")

in this problem attainment is 1.0067 and reached is 1.

On other rows where attainment is 1.01 or 1.02 and the reached is 1.0167 or 1.0267 it works perfect.

Does this make since?

[JamesDeckert]

Your first post info doesn't really agree with your second post, nor what the thread heading is implying. This makes helping you a challenge.
Are you saying that the "False" part of the if works, but the "True" doesn't?
You are returning "True" and "False" from the iif, so is what you're returning them to expecting a string or a boolean? If boolean, they shouldn't be included in quotes.

[ahoneycutt]

The true and false has nothing to do with my question. I was just trying to show you the only "code" I am using. Sorry for the confusion.

The issue is the formatting of the number not the formula. it is a number set to double and auto decimal places.

Access shows 100% as a 1 and my number is a 1.0069 that I am checking for so when I ask it if it the attainment(1.0069) is >= reached(1) it does not see that 1.0069 which is 100% is the same as 1 which is also 100% so it returns a false.

I just need the 1 to display as 1.00 but no matter how I format it Access only displays 1.00 as a 1 only.

I found a workout in my data but I would still like the answer if possible.

[JamesDeckert]

formatting the number doesn't change the underlying value. When checking for > or < with a real number, you shouldn't have a problem. When check for =, real numbers may not be equal even though they look equal because of their precision. You can format so that 1.00 looks like 100% on a report, form or query. No problem using the format function. If you want to test for = you'll need to round both values Round ( expression, [decimal_places] ).
1.0069 is not equal to 1, therefore Access won't evaluate it as such. but round([field1],2)=round([field2],2) should be able to test for equality to 2 decimal places.