Funky Math

[pvwdr]

I have a query that subtracts two fields
fieldA, fieldB, fieldA-fieldB
if fieldA = 100 and fieldB=100 then filedA-fieldB should = 0 ...
well instead of 0 I get -9.09494701772928E-13 ... help, this is making my reporting all screwed up. Has anyone ever seen this in Access XP ... thanks

 

[GKProgrammer]

Hi pvwdr,

You should provide the SQL for this Query it would help to diagnose the problem.



Regards,
gkprogrammer

 

[pvwdr]

Here is the sql:
SELECT DISTINCT tmpCharge.PATID, tmpCharge.financial_class_value, tmpCharge.SumOfguarantor_liability AS Charges, IIf(IsNull([SumOfpayment_amount]),0,[SumOfpayment_amount]) AS Payments, [Charges]-[Payments] AS Balance
FROM tmpPay RIGHT JOIN tmpCharge ON (tmpPay.financial_class_value = tmpCharge.financial_class_value) AND (tmpPay.PATID = tmpCharge.PATID);

It is the balance field that gets the funky number when it should be zero

 

[JoeCool71]

I'm not sure if this will help or not, but in working with Access over the past 5 years I have noticed some things that can not be explained. Your code above is one of them. You are creating a field in your SQL called Payments and then you are trying to pass this argument in the next field. This does not work in normal SQL. You could do this in code, but since you are writing it this way, please try the following.

SELECT DISTINCT tmpCharge.PATID, tmpCharge.financial_class_value, tmpCharge.SumOfguarantor_liability AS Charges, IIf(IsNull([SumOfpayment_amount]),0,[SumOfpayment_amount]) AS Payments, [Charges]-IIf(IsNull([SumOfpayment_amount]),0,[SumOfpayment_amount]) AS Balance
FROM tmpPay RIGHT JOIN tmpCharge ON (tmpPay.financial_class_value = tmpCharge.financial_class_value) AND (tmpPay.PATID = tmpCharge.PATID);

Instead of passing Payments field you will have to redo your logic for calculating Payments in your Balance field.

Hope this works.

Jim

 

[yamafopa]

I am with Joe on this. I'll bet that one or both of Charges or Payments has a data type of 'Double'. I can tell you that (but not why) double values do not hold exact values. I have seen many occasions where I am trying to evaluate two Doubles against each other and it doesn't work. This will always turn out to be because the value that I think should by, say 10.24 actually evaluates to 10.23485672 (or some other equally stupid thing). While I have never found a good explaination of this I can tell you that Casting and Rounding procedures can help with this sort of thing.

Hope this helps

yamafopa!

 

[oharab]

Try converting your "Double" field types to currency.
It has worked for me in the past.

hth

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[yamafopa]

Gotta watch those Currency types as well. They display only two decimal places, but actually hold four.

yamafopa!

 

[jmeadows7]

This part of using currency and doubles really bytes. I've had to use a few work arounds - the one that works the best is to put an iif statment in the query.

iif([fielda] -[fieldb]< 0,0,[fielda]-[fieldb])

If your number can actually be negative - you may want to plug in the lowest value that it can be to the right of the < sign - i.e. < -.001

This will slow downt he query just a hair, but it seems to do the trick most of the time.

HTH

 

[pvwdr]

Well, what I ended up doing was something a bit different ... this query was a make table query, instead of that I made it an append query and just emptied teh table before I appended, this way I could make the field type in the table currency; this has solved the problem.

THanks for all of your advise,
Bill