using less than in criteria in select query

[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

 

[Michel Walsh]

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

 

[Guest]

If you use "0.0" then it is looking for text - not a number.

Try using <0 without the quotes.

 

[John Spencer]

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

 

[Guest]

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

Fieldreweight
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

 

[Guest]

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

 

[Guest]

I haven't tried <= but I have tried < with the fields being long integer
fields, and nothing happens. I'm stumped.
Anne

 

[Guest]

Do you get any results if you omit the criteria?

 

[Guest]

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

 

[John W. Vinson]

I haven't tried <= but I have tried < with the fields being long integer
fields, and nothing happens. I'm stumped.

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]