Prev: Mid string null value
Next: Updating text field to new numeric field & moving minus sign to fr
From: jo on 20 May 2010 15:15
Hi
Thank you for your reply's.

I tried the suggested update to my query but unfortunately it did not
display the correct result.

Hence to simplify fault finding the problem. I created two tables, a query
and a form / subform based on the example code we have been discussing.
Using test data so that Record 2 of table A would be flagged as it did not
have a Y in the colour field but did have a tick in Received field resulted
in no record returned by the query.
(No record returned at all, of any type)


I have supplied the following information as I must be overlooking
something.

Copy and pasted

SELECT A.Date_R, A.Received, A.ID, B.Colour
FROM A INNER JOIN B ON A.Main = B.Main
WHERE A.Received=-1 AND B.Colour <>"Y";



Table A has the following fields and data types

Date_R >>> Date/Time
Received >>> Yes/No
ID >>> Text
Main>>> AutoNumber>>> PK Field

Record 1 has the following data
Date_R shows 02/02/2010
Received shows Tick
ID shows 2
Main shows 1

Record 2 02/02/2010, Tick, 2, 2


Table B has the following fields and data types


IDNo >>> AutoNumber >>>PK Field
Main >>> Number
Colour>>> Text

Record 1 has the following data
IDNo shows 1
Main shows 1
Colour shows Y

More Background

Relationships is one to many

Table A set to main
Related table/ query set to main

Join properties option 1

Enforce ... selected
From: Marshall Barton on 20 May 2010 17:32
jo(a)jo.uk wrote:
>I tried the suggested update to my query but unfortunately it did not
>display the correct result.
>
>Hence to simplify fault finding the problem. I created two tables, a query
>and a form / subform based on the example code we have been discussing.
>Using test data so that Record 2 of table A would be flagged as it did not
>have a Y in the colour field but did have a tick in Received field resulted
>in no record returned by the query.
>(No record returned at all, of any type)
>
>
>I have supplied the following information as I must be overlooking
>something.
>
>Copy and pasted
>
>SELECT A.Date_R, A.Received, A.ID, B.Colour
>FROM A INNER JOIN B ON A.Main = B.Main
>WHERE A.Received=-1 AND B.Colour <>"Y";
>
>Table A has the following fields and data types
>Date_R >>> Date/Time
>Received >>> Yes/No
>ID >>> Text
>Main>>> AutoNumber>>> PK Field
>
>Record 1 has the following data
>Date_R shows 02/02/2010
>Received shows Tick
>ID shows 2
>Main shows 1
>
>Record 2 02/02/2010, Tick, 2, 2
>
>Table B has the following fields and data types
>IDNo >>> AutoNumber >>>PK Field
>Main >>> Number
>Colour>>> Text
>
>Record 1 has the following data
>IDNo shows 1
>Main shows 1
>Colour shows Y
[snip]


Your query will not return any records because tblA record 1
joins with rblB record 1 but is filtered out because colour
= Y.

tblA record 2 does not join to a record in tblB so the
query's colour field contains Null. It is very important to
understand that Null kind of represents that the value is
**unknown**. As such, you can not ever say that an unknown
value is either equal or not equal to any other value, not
even another unknown value. Do this record is filtered out
because you can not say that a Null colour is not equal to Y

If you want to return records from tblA when the colour fiel
is Null, then you have to do something to allow for that.
This is one way:

WHERE A.Received=-1 AND (B.Colour <>"Y" Or B.Colour Is Null)

Note that those parenthesis are needed to get the And and Or
to be evaluated in the right order.

--
Marsh
MVP [MS Access]
From: KARL DEWEY on 20 May 2010 17:59
>>I tried the suggested update to my query but unfortunately it did not
display the correct result.
What was the results? Can't fix without knowing what's wrong.

What was the output?

--
Build a little, test a little.


"jo(a)jo.uk" wrote:

> Hi
> Thank you for your reply's.
>
> I tried the suggested update to my query but unfortunately it did not
> display the correct result.
>
> Hence to simplify fault finding the problem. I created two tables, a query
> and a form / subform based on the example code we have been discussing.
> Using test data so that Record 2 of table A would be flagged as it did not
> have a Y in the colour field but did have a tick in Received field resulted
> in no record returned by the query.
> (No record returned at all, of any type)
>
>
> I have supplied the following information as I must be overlooking
> something.
>
> Copy and pasted
>
> SELECT A.Date_R, A.Received, A.ID, B.Colour
> FROM A INNER JOIN B ON A.Main = B.Main
> WHERE A.Received=-1 AND B.Colour <>"Y";
>
>
>
> Table A has the following fields and data types
>
> Date_R >>> Date/Time
> Received >>> Yes/No
> ID >>> Text
> Main>>> AutoNumber>>> PK Field
>
> Record 1 has the following data
> Date_R shows 02/02/2010
> Received shows Tick
> ID shows 2
> Main shows 1
>
> Record 2 02/02/2010, Tick, 2, 2
>
>
> Table B has the following fields and data types
>
>
> IDNo >>> AutoNumber >>>PK Field
> Main >>> Number
> Colour>>> Text
>
> Record 1 has the following data
> IDNo shows 1
> Main shows 1
> Colour shows Y
>
> More Background
>
> Relationships is one to many
>
> Table A set to main
> Related table/ query set to main
>
> Join properties option 1
>
> Enforce ... selected
> .
>
From: jo on 21 May 2010 10:13
Hi, Marsh

I tried your example unfortunately it did not return any records from Table
A when the colour field is Null in Table B
(No records were return at all)
From: Marshall Barton on 21 May 2010 10:56
jo(a)jo.uk wrote:
>I tried your example unfortunately it did not return any records from Table
>A when the colour field is Null in Table B
>(No records were return at all


Your example had no records in Table B with Null in the
Colour field so I'm not clear about what you did.

OTOH, if you added such a record, I would expect the query
to find it, so I guess I need to see the query as you tried
it.

You might want to try debugging the query by removing
various parts of the where clause to verify each condition
separately.

--
Marsh
MVP [MS Access]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Mid string null value
Next: Updating text field to new numeric field & moving minus sign to fr