Is Null Problem

[jamesnkk]

I have a textfield called "POComplete" in my table - Vendor,
when the po is completed, it will write "OK" onto this field, otherwise
the "POComplete" is blank or null.

So I am trying to list out all outstanding PO using query,
In the Criteria - Field -"POComplete" , I indicate Is Null. When I run the query,
I realize not all outstanding PO was listed out. what wrong ?, only 90 records
out of 109 records was listed.


I physically count the table, Total records in the Vendor table was 164,
55 records indicate "OK" in the "POComplete" , (164 - 55) therefore 109 records
should be outstanding.But only 90 records was listed out, 19 records was missing.


Next, this time round, I change the Criteria to- Is Not Null, surprise to
see that some of the POComplete which is blank or null is displayed together
with those not null records. Additional 19 null records was display.

Is the table corrupted ?

[Megalog]

Your table is fine..
You have 90 nulls, and 74 with values (19 of those being blank and 55 having "Ok").

So the criteria for outstanding PO's would be:
isnull(POComplete) = true OR POComplete = ""

Completed PO's would be the opposite:
isnull(POComplete) = false AND POComplete <> ""
or
POComplete = "OK"

[jamesnkk]

Your table is fine..
You have 90 nulls, and 74 with values (19 of those being blank and 55 having "Ok").

So the criteria for outstanding PO's would be:
isnull(POComplete) = true OR POComplete = ""

Completed PO's would be the opposite:
isnull(POComplete) = false AND POComplete <> ""
or
POComplete = "OK"

Thanks you for your reply, but I don't understand why would 19 records with blank consider as value instead of null ? this 19 records are filled with other information, only the field - "POComplete" is blank.

I tried to put your statement under the field in the criteria column, but nothing come out.

[jamesnkk]

Your table is fine..
You have 90 nulls, and 74 with values (19 of those being blank and 55 having "Ok").

So the criteria for outstanding PO's would be:
isnull(POComplete) = true OR POComplete = ""

Completed PO's would be the opposite:
isnull(POComplete) = false AND POComplete <> ""
or
POComplete = "OK"

I think I know why, in my statement I wrote , if po not complete I would write pocomplete="", so that cause the different between "" and null.

For O/S PO -
I insert a column for POComplete, in the Criteria = ""
Next I insert another column- IsNull([povendor].[pocomplete]=True)
Then in the "Or" column - <> False
-----------------------------------------------------------------------------------------------------
For Completed PO

I insert a column for POComplete,
In the "Or" column - <> "", I think this statement would take care of null and ""

[NeoPa]

Just a minor point, but in SQL it's better to use the form

X Is Null

than

IsNull(X)

Is Null is intrinsic whereas IsNull() is a function that needs to be called (for every record generally).

[jamesnkk]

Just a minor point, but in SQL it's better to use the form

X Is Null

than

IsNull(X)

Is Null is intrinsic whereas IsNull() is a function that needs to be called (for every record generally).

Thanks I learned something new

[NeoPa]

Glad to help James :)