LEFT join query - right table doesn't appear returns #Error

[johnseito]

I did a left join query to show everything on the left table, however if something on the right table doesn't appear. The field returns #Error.



How do I get rid of it ?

[pbaldy]

What is the SQL? I'd expect Null, not an error. Are you doing a calculation?

[johnseito]

actually is like a calculation.
What I have with the table from the right is a number in % and then I convert it into a value by using this function val(field) - (to do field1-field2).
After that, when I run the query all the ones that didn't match are in #Error.

[johnseito]

I actually got it to remove the error by doing some iif statements - such as iif field1 is > 0 and isnumeric(field2) then field1-field2.

However, I got some weird calculations

-- 23.98 - 24 = -2.000000000000031
-- 50.99 - 51 = -9.999999999999999

I don't know why, it should have been -.02 for the first one, and -.01 for the second one.

-- 25.03 - 25 = 3.0000000000000000000

should be .03

I don't know why, if someone could explain ?

[pbaldy]

What is the actual SQL or calculation?