Var Function Error?

[leishtheman15]

Hello

I'd like some advice as I'm not sure if I'm going mad or not.

I've wasted around 3 frustrating hours today on what should have been a simple query expression which refused to generate the correct answer. I was using the below, with val used to convert the two currency fields [ExtendedPrice] and [UnitPrice] to a number to match [Qty]:

flag: IIf(Val([Test2]![ExtendedPrice])/Val([Test2]![UnitPrice])<>[Test2]![Qty],1,0)

This refused to work. I eventually found a solution by combining the result of ([Test2]![ExtendedPrice])/([Test2]![UnitPrice] within a single Val statement:

flag: IIf(Val([Test2]![ExtendedPrice]/[Test2]![UnitPrice])<>[Test2]![Qty],1,0)

I have absolutely no idea why the top statement refused to work when the bottom one did work?

Can anybody explain???

Many thanks,

Andy

 

[xenou]

What are the data types for ExtendedPrice, UnitPrice, and Qty? Can you provide a sample set of values?

 

[leishtheman15]

Hi

[UnitPrice] and [ExtendedPrice] are just set to 'currency' data type, and [Qty] set to Number (long integer) data type.

If you create a really simple table containing these 3 fields, then plug some numbers in, e.g. UnitPrice = $1.40, ExtendedPrice = $26.6, Qty = 19, you should experience the same problem when you create the same query expression..

Andy

 

[xenou]

Your problem is most likely a result of doing arithmetic with floating point numbers. For example you can even use your original formula but just round the result and it also works:
flag: IIf(Round(Val([ExtendedPrice])/Val([UnitPrice]),0)<>[Qty],1,0)

In short, whenever you are working with floating point numbers you are in danger of losing precision and a value that you think is something like 5.46 might really be 5.460000000000000000001. A result of this is that the number is not equal to what you think it is equal to, namely 5.46. An example of the very same thing in Excel is here: Floating Point Errors in Excel - Easy Excel Tutorial

You may ask why you don't see this difference when you display the numbers. Well, numbers are rarely displayed with a zillion decimal places after them and the tiny variations very often just don't show up in the displayed values. But they are real nonetheless. At a very basic level, a floating point (decimal) number is a different animal than an integer. Some datatypes do exist out there that are made to preserve decimals precisely, but from the looks of things, I'm going to hazard that Access's Currency is not one of them.

A couple of things come up here which I guess I feel duty bound to point out.

• There is no reason to use Val() here. Read the help on that function - it takes a string as its argument and you aren't working with strings.
• If you ever run into a case where ExtendedPrice is zero your formula will error. Since IIF() evaluates the entire expression before returning (both the true and false parts), you cannot use it to test for zero in the denominator either (so don't try a nested IIF to check for zero in the denomination, as that won't work).
• Not really a problem but consider if Qty should be a floating point data type also - there may come a day when you may want to sell or buy 1.5 of something, or when units are converted from various measurement systems and so you have to use decimals to get the right amount in one measuring system to match what is in the other).
• If your database is not storing extended price as unit price * qty you have a serious bug that needs to be fixed. In general, extended price should not even be stored in the database. It should be calculated from the other two. Some business systems will store extended price anyway - for ease and possibly shaving a few microseconds off of query times. But even then, the value is calculated from the other two and cannot be anything other than Qty * Unit Price. I hope you are just doing some cleanup and this is not going to happen anymore. However, whatever it is that is going on, using rounding is the best way to go, I think, in order to peel off the very very very tiny differences that may exist between the value of a floating point number and an integer.

 

[leishtheman15]

Thanks for the response.

Interesting argument about the floating point - I just wonder why the expression calculates correctly when [ExtendedValue] and [UnitPrice] are encased in a single Val function and fails when they're not though, as I'd have expected the same behaviour in both scenarios if floating point was the cause of the problem.

I used Val because they expression refuses to work without it.

Andy

 

[xenou]

It could be some (undocumented) side effect of val(). Or perhaps some vagary of floating point arithmetic. I would always try to avoid using comparisons of equality or inequality with the results of floating point arithmetic, or use rounding to be safe. It's one of those grey areas. With Excel I can work for two years and all goes well (I suspect they have some things built into the code to smooth this over, given that 99% of their users have probably never heard the term floating point). But then suddenly it pops up and I'm reminded of the fundamentals again. Good to be reminded