compare two values rounding problem

[BCJourney]

I have an unmatched query that returns records where query1.value1 <> query2.value1


The problem is that the numbers in their source tables are both formated as field size: Double, Format: 0% but the query will return the records as unmatched anyway. I'm sure this has to do with when the tables were imported, one value ran 10 decimal places and the other table only 2. I thought that since they have been formatted the same after import that it would be recognized as the same number. Any suggestions?

[JoeM]

The formatting features you listed only affect how the data is displayed, not the actual value held in the field. You can store up to 10 decimal places, but only display 2.
If you want to compare them in an unmatched query, first create a query using the round function in a calculated field to round the 10 decimal place values to 2. Then use that query in a new query with your other table and it should work.

[BCJourney]

Thank you for your help. I did that and that may work fine, but one table rounds up while the other one rounds down, thus creating a mismatch. Can you help?

[JoeM]

Then create creates for both of them, rounding them both the way you like, and then perform an unmatched query between those two queries to get what you need.

[BCJourney]

I did queries for both. when i round one to percent with 0 places, it shows that way but when you click on it after the query is run it shows a million decimal places. any other suggestions?

[JoeM]

Where are you rounding them? Rounding them in the Properties or Format is no good. That only changes what is displayed, not what is actually stored or used in calculations.
You need to round them in calculated fields in your query before comparing them, i.e.
MyRoundedField: Round([FieldName],2)

Create new queries rounding the field in each table, then use these two queries in your unmatched query, matching on the new "rounded" fields.

[June7]

Also, be aware that Round() function in Access uses an even/odd rule (aka banker's rounding):

Round(3.144,2) = 3.14
Round(3.145,2) = 3.14
Round(3.146,2) = 3.15
Roune(3.175,2) = 3.18

[BCJourney]

I think I got it now. Thank you for you patience.