# Thread: problem with double number

1. Registered User
Join Date
Jan 2009
Posts
11

## Unanswered: problem with double number

Hi

This problem is driving me crazy. Please someone help. I have table in MS access that is structured like this:

test_tbl

id - autonumber
weight - number (double, 2 decimals)
price - text

This is my data in this table

Code:
```id   weight   price
1    5          200
2    10        250
3    15        300```
Can anyone tell me what is wrong with my query:

Code:
`SELECT price FROM test_tbl WHERE weight >= 8,29 AND weight <= 8,29;`
MS Access returns me nothing for that query. I tried and this also:

Code:
`SELECT price FROM test_tbl WHERE weight >= 8.29 AND weight <= 8.29;`
But I got the same result. Please someone help.
Last edited by tbjornen; 04-03-11 at 13:23.

2. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Your second example, using a Decimal Point instead of a Comma, is the correct one, unless you're working in a country whose Regional setting uses commas to denote the decimal portion of the number. The problem is in your logic in the WHERE clause.

weight >= 8.29 AND weight <= 8.29

actually evaluates to

weight = 8.29

and you'll only return a value if you have a record where weight is equal to 8.29, and no record fits that criteria, hence no records are returned.

Linq ;0)>

3. Registered User
Join Date
Jan 2009
Posts
11
Yes of course ... I didn't see that :P

Do you have any idea-a how to pull right price from that table ? In this example it should get price 200, because 8.29 is between 5 and 10 and it's smaller then 10 so it's category 5.

Can you tell me how to write a query that will return that.

Thanks

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
how does the system 'know' what is the right price?
answer that and you have the SQL

Im guessing you may need to use select top AND set the order to weight descending

effectively
select top 1 price from mytable
where weight <= <atargetvalue>
order by weight DESC

5. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
also consider possible datatype issues.

8.29 cannot be represented precisely by floating point numbers such as Double

...you can get very close:
8.290 000 000 000 000 027 755 575 615 628 912 416 259 000 225 882 555 595 829 525 121 934 260 593 835 358 005 258 136 482 860 860

a humble Double has nowhere near that number of decimals but it also cannot "=" 8.29

try Currency instead (is a scaled integer and precise to 4 decimals)
(try Currency for your price as well (instead of text!!!))

izy

6. Registered User
Join Date
Jan 2009
Posts
11
Originally Posted by healdem
how does the system 'know' what is the right price?
answer that and you have the SQL

Im guessing you may need to use select top AND set the order to weight descending

effectively
select top 1 price from mytable
where weight <= <atargetvalue>
order by weight DESC
That's the right answer. Thank you very much.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•