The difference between 117.50 and 117.5 !!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

This one is driving me mad. I've got a query looking at a table which has
two values 100.00 and 17.50 (for example). The query adds the two together
and should return any with the total of 117.50. It doesn't. It returns
nothing. If the value I want to look for is 117.5, however, it returns all
of the 117.50 records. Eh? How's that work? Whatever I do, I cannot get an
entry of 117.50 to return any records. I've tried formatting the query
fields (the table fields are set to Double, Fixed and 2 places), but it
doesn't make any difference!

Thanks (sigh).
 
If your fields are single or double there may be a very slight difference that
you are not seeing. Floating point numbers cannot precisely reflect the
decimal portion. You might try looking for a difference

WHERE Abs(YourAddition - 117.50) < .000001

Or if you don't need more than four decimal places change the field type to
currency.

Or use
WHERE CCur(YourAddition) = 117.50

If none of those work for you, I suspect that you've managed to turn the value
into a string somehow.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Hi

This one is driving me mad. I've got a query looking at a table which has
two values 100.00 and 17.50 (for example). The query adds the two together
and should return any with the total of 117.50. It doesn't. It returns
nothing. If the value I want to look for is 117.5, however, it returns all
of the 117.50 records. Eh? How's that work? Whatever I do, I cannot get an
entry of 117.50 to return any records. I've tried formatting the query
fields (the table fields are set to Double, Fixed and 2 places), but it
doesn't make any difference!

Thanks (sigh).
Click to expand...

Please post the SQL view of your query. My guess is that you're having a
problem with "roundoff error" - a Single or Double number value is actually
stored as an approximation, so what's stored might actually be 117.49999999993
or 117.50000000002 or the like. Or you might be using the Format() function,
which returns a text string instead of a number. If you only need two decimal
places, and especially if these are money values, I'd use a Currency datatype
rather than any sort of Number.
 
Hi - and thank you for your replies.

I have changed the table's data type to currency and the result is the same.
The SQL view is:

SELECT *
FROM tblData
WHERE ((([tblData].[TGoods]+[tblData].[TVat])=[Forms]![frmInput]![Text17]));

If, instead of the Text17 bit, I type 117.5 it works. If I type 117.50 into
the criteria box, it changes it to 117.5 - and works.
I also have another query that works with just the TGoods bit. I think it
relates to the adding together of TGoods and TVat that causes the problem.

Andy.
 
Andy, the problem is about data types as John and John explained.

I understand you opened your tblData in design view and changed *both*
TGoods and TVat fields to Currency. But currency stores 4 decimal places.
Therefore the data stored in the Currency field may not be what you actually
see. To address this:

1. Create a query using this table.

2. Change it to an Update table (Update on Query menu.)
Access adds an Update row to the query design grid.

3. In the Update row under the TGoods field, enter:
Round([TGoods],2)
In the Update row under the TVat field enter:
Round([TVat],2)
Run the query.
Now the data in the table is correct.

4. The next thing is to ensure Access understands the text box correctly. If
it is unbound, open the form in design view, and set the Format property of
Text17 to:
Currency

5. Now you need to ensure the query understands the data correctly. In query
design view, choose Parameters on the Query menu. Access opens a dialog.
Enter:
[Forms]![frmInput]![Text17] Currency

Now you have:
a) the right data in the table,
b) the right data type for the text box,
c) a numeric comparison in the query.
You have therefore addressed the various places where a misunderstanding
could arise.
 
Hi Allen.

Thank you!! I've been using Access for years and never come across something
like this! I appreciate your feedback. Once I set the form field property to
currency, it worked beautifully!

Andy.

Allen Browne said:
Andy, the problem is about data types as John and John explained.

I understand you opened your tblData in design view and changed *both*
TGoods and TVat fields to Currency. But currency stores 4 decimal places.
Therefore the data stored in the Currency field may not be what you
actually see. To address this:

1. Create a query using this table.

2. Change it to an Update table (Update on Query menu.)
Access adds an Update row to the query design grid.

3. In the Update row under the TGoods field, enter:
Round([TGoods],2)
In the Update row under the TVat field enter:
Round([TVat],2)
Run the query.
Now the data in the table is correct.

4. The next thing is to ensure Access understands the text box correctly.
If it is unbound, open the form in design view, and set the Format
property of Text17 to:
Currency

5. Now you need to ensure the query understands the data correctly. In
query design view, choose Parameters on the Query menu. Access opens a
dialog. Enter:
[Forms]![frmInput]![Text17] Currency

Now you have:
a) the right data in the table,
b) the right data type for the text box,
c) a numeric comparison in the query.
You have therefore addressed the various places where a misunderstanding
could arise.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi - and thank you for your replies.

I have changed the table's data type to currency and the result is the
same.
The SQL view is:

SELECT *
FROM tblData
WHERE
((([tblData].[TGoods]+[tblData].[TVat])=[Forms]![frmInput]![Text17]));

If, instead of the Text17 bit, I type 117.5 it works. If I type 117.50
into the criteria box, it changes it to 117.5 - and works.
I also have another query that works with just the TGoods bit. I think it
relates to the adding together of TGoods and TVat that causes the
problem.
Click to expand...
Click to expand...
 
Excellent news.

As you found, it is a matter of eliminating all the places where the data
could be misunderstood. Setting the Format property of the unbound text box
is the way to give Access information about how to interpret the data type
there.

Similarly, explicitly declaring the parameter in the query tells JET the
data type there.
 
Back
Top