using numbers as characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LAF - 26 Mar 2006 23:49 GMT

I have a field that is a character field, and "0" represents 0, " "
represents Null. The problem is that when I use <> "0" in a query it returns
all the fields with "0". If I use Is Null, then all fields are returned that
are not empty. I change the field to numeric for data analyses. Question:
Why is the query returning fields with "0" when the criteria is <> "0" ?

Thanks,
LAF

Reply to this Message

Jerry Whittle - 26 Mar 2006 23:49 GMT

I'm confused. Is that field now a Text or Number datatype in the table? How
did you change the the data from Text to Number?

Are you actually storing in the "0" with quotation marks?

" " is not null. It's a space. Even "" is not null; rather it's an empty
string.

Please show the actual SQL that you are using.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a field that is a character field, and "0" represents 0, " "
> represents Null. The problem is that when I use <> "0" in a query it returns
[quoted text clipped - 4 lines]
> Thanks,
> LAF

Reply to this Message

LAF - 26 Mar 2006 23:46 GMT

Thanks, Jerry. Here is more information. For the moment, please disregard
the number/text issue. The problem is with the query.

The field is a text field with length 2. It is used to store number of
broken tail feathers in birds. For some birds this is not inspected.
Therefore the field can be empty (no inspection = null), have a 0, 1, 2, etc.
The 0, 1, 2, ... are aligned to the left, as expected because this is a
text field. For making a query, it should not matter if the field is next or
number. My problem is with the query.

For example, if the criteria is ="0", I just get the rows with 0 in the
field. If the criteria is ="1", I just get the rows with 1 in the field.
This is fine and as expected. However, if the criteria is <>"0", I get all
the rows with 0, as well as all the other rows. The same thing happens with
<>"1"; I get the rows with 1. The Is Null criteria gets just null records.
The Is Not Null criteria just gets records with entries. I do not understand
why the <> is not working as expected.

In SQL view, the query appears as:

SELECT HAKBAND6.TBROKEN
FROM HAKBAND6
WHERE (((HAKBAND6.TBROKEN)<>"0"));

LAF

> I'm confused. Is that field now a Text or Number datatype in the table? How
> did you change the the data from Text to Number?
[quoted text clipped - 14 lines]
> > Thanks,
> > LAF

Reply to this Message

Jerry Whittle - 26 Mar 2006 23:42 GMT

I checked your query and it works fine EXCEPT if there is a space in the
string such as " 0". Try this to see if any of them have leading or trailing
spaces. If they do, you'll need to fix the data or take it into account.

SELECT HAKBAND6.TBROKEN, InStr([TBROKEN]," ") AS Spaces
FROM HAKBAND6
WHERE (((InStr([TBROKEN]," "))>0));

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Thanks, Jerry. Here is more information. For the moment, please disregard
> the number/text issue. The problem is with the query.
[quoted text clipped - 40 lines]
> > > Thanks,
> > > LAF

Reply to this Message