Results 1 to 6 of 6
  1. #1
    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. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    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)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    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
    currently using SS 2008R2

  6. #6
    Join Date
    Jan 2009
    Posts
    11
    Quote Originally Posted by healdem View Post
    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
  •