Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    30

    Unanswered: Criteria in query not working for number

    This should be very simple, but for some reason it's not filtering data. I have a simple query that I want to pull data from a field [Price] that is greater than 4000. Criteria should be >4000. How simple is that? But it's pulling everything. I have another number field that I'm putting something similar in [Quantity] >10 and it's working fine and pulling all data greater than 10. The only difference is how the fields are defined in the table. Price is a number field, field size = decimal, precision =15 (not sure what this is), scale = 5 (not sure what this is either), decimal places = Auto
    Quantity field is a Number, but is double and does not have precision or scale. What am I doing wrong?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Precision controls how many digits are stored (3.14 as compared to 3.14152, for exampe). Scale controls how many digits are stored to the right of the decimal.

    Why not just set the field to be "Currency" type if that's what it is?
    Me.Geek = True

  3. #3
    Join Date
    Feb 2009
    Posts
    30
    The tables are from a vendor through ODBC and I'm just creating reports and queries from it, so I can't change the field in the table. Based on your explanation, I would still think that >4000 should still work. If I were to put in zero's to the right of the decimal, it would just truncate them anyways before running. Any suggestions to try?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That should work for any type of numeric field. Are you sure there isn't an OR in the criteria that's causing other records to be returned?
    Paul

  5. #5
    Join Date
    Feb 2009
    Posts
    30
    No, there's no OR in there. I created it from scratch and I went down to teh most basic form. Looks like it may be a problem with the database then, I'll have to hit up the vendor. Thanks.

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    What exactly does your SQL string look like? It's hard to diagnose the problem without it.
    Me.Geek = True

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you able to format the column in the query designer? (ie. right-click on column and you'll see a popup.) If so, you could try that or try using an expression -ie: MyPrice: Format([Price],"#") or something like MyPrice: Format([Price],"Currency").
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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