RE: Query returns no rows when <> 0 applied

Duane, you are a star. Your question provided the solution.

If I Updated the field with a "" (double quote) it got rid of the Null flag
and I get the records. So it had to do with the Null value.

I do not think I have ever encountered this before in the long list of
Access solutions I have built. I do understand the behaviour now, as I was
asking the query for anything that's different from 0, but there was nothing
available as all other rows were Null, so it did the right thing, but is this
the case with an Access 97 or 2000 database as well? I am little confused!

--
Leo


"Duane Hookom" wrote:

Is the field possibly Null?
--
Duane Hookom
Microsoft Access MVP


"Leo" wrote:

Actually, this is not working only on the 'Not', '<>', 'Not Like' statements
- so statements that exclude specific or a range of data are not working, the
query comes back empty. I also tried to build it through code, but I am
getting the same results.
--
Leo


"Leo" wrote:

Hi Jerry. Thanks for the reply. Not at all, this table is in Access. It's an
.mdb file, so nothing special about the database. I tried it on another
machine, with Access 2003 installed, but got the same result. I tried
importing the table in a fresh database, but I am getting the same result!
--
Leo


"Jerry Whittle" wrote:

That is strange. Is this table in Access or something like SQL Server?

Will the following work?
Not 0
<0 Or >0
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Leo" wrote:

Here's my query:

SELECT TBL_RAW_IMPORT.EXCLUDE_FLAG
FROM TBL_RAW_IMPORT
WHERE (((TBL_RAW_IMPORT.EXCLUDE_FLAG)<>0));

EXCLUDE_FLAG is an Integer field

The thing is, I get no records returned at all, but this returns all the
records with 0s in it

SELECT TBL_RAW_IMPORT.EXCLUDE_FLAG
FROM TBL_RAW_IMPORT
WHERE (((TBL_RAW_IMPORT.EXCLUDE_FLAG)=0));

Another abnormal behaviour is using Not Like '*EXCLU*' for example on
another field. Not Like returns nothing, but using just Like returns the
appropriate records.

I am using Access 2003, and I have never encountered this behaviour before.

Has someone experienced this before? Can someone lead me to a possible
solution? Is it a setting in access I have to change? I am totally lost!!!!

--
Leo
.

Relevant Pages

  • RE: Query returns no rows when <> 0 applied
    ... Microsoft Access MVP ... "Leo" wrote: ... query comes back empty. ... so nothing special about the database. ...
    (microsoft.public.access.queries)
  • Re: Refer to a form in a pass-through query
    ... The name of the query is PKLQ. ... "Duane Hookom" wrote: ... SQL in Access. ... A pass-thru query sends exactly what is coded to another database engine, ...
    (microsoft.public.access.queries)
  • Re: Experts Who Will Not Correct Errors...
    ... The same thing is eminently true of Leo van de Pas's database -- ... Leo even puts living people and recently deceased people up on his ... THEIR ANCESTRY. ... Peter Stewart, for one, made it QUITE CLEAR to me he did NOT want to be ...
    (soc.genealogy.medieval)
  • Re: Bastardized children of Elizabeth Owen, wife of Sir Thomas Burgh
    ... However M. Sjostrom was not criticizing Leo. ... Sjostrom was pointing out that since Leo's database does not include some ... That isn't a criticism of Leo. ... It was a criticism directed at Douglas' ...
    (soc.genealogy.medieval)
  • Re: Refer to a form in a pass-through query
    ... Duane - you mentioned that I need to modify the SQL property of the p-t ... The name of the query is PKLQ. ... A pass-thru query sends exactly what is coded to another database ...
    (microsoft.public.access.queries)