using less than in criteria in select query

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

Guest

Hello!
I have a table where I need to evaluate if one field (pre-weight) is less
than 0. I tried doing an select query where I said

< "0.0"

in the criteria box but it would not select any records, even when I had set
one to be 0.000. Is there some other way to do this?
Thanks,
Anne
 
I would have tried


< 0


without quotes. If Access added the quote, maybe the field, or the
expression, is a string, NOT a number, and < "0" is like < "s" somehow,
ie, you are asking for dictionary ordering, while it seems you seek a
numerical ordering. Can you check in design view, of the table, if the field
is numeric?



Hoping it may help,
Vanderghast, Access MVP
 
If you use "0.0" then it is looking for text - not a number.

Try using <0 without the quotes.
 
First, preWeight should be a number field.

If that is case the criteria should be < 0. OR perhaps it should be <= 0
(if you want zero weights in addition to those less than zero).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks! I went in and made sure the field was a number field. I also made
sure it said 0. Then I ran the select query that says

Field:Preweight
Table:Table1
Sort:
Show: (checkbox is yes)
Criteria: <0

I also have it showing me the filter number if the preweight is less than
zero. But no filter numbers show up when I run the query.

Thanks,
Anne
 
Hello! I did so, and still don't get anything in the select query. I have
made sure my fields are number fields, too.
Anne
 
I haven't tried <= but I have tried < with the fields being long integer
fields, and nothing happens. I'm stumped.
Anne
 
Oddly enough (or maybe not oddly), when I changed the number from long
integer to double, the query works. I wonder why? I have now experimented
 
I haven't tried <= but I have tried < with the fields being long integer
fields, and nothing happens. I'm stumped.
Click to expand...

Do you have records in the table with values of preweight such as -1,
-11, -8? They should show up with a criterion of <0. Zero is, of
course, NOT less than zero, so zero values won't show up; similarly,
NULL (missing, unspecified) values are not less than zero (or greater
than zero, or unequal to zero, or ANY other comparison). The only
criterion which will find records with a NULL preweight is

IS NULL

so perhaps you could try

<= 0 OR IS NULL


John W. Vinson [MVP]
 
Back
Top