JOIN QUERY

Z

zyus

I hv two tables as follow

Table1 Table2
FieldSex FieldSex FieldDes
---------- ---------- ---------
null null unknown
m m male
f f female

When i join this table in query those null record cannot be display.
Supposed null record will be displayed as unknown. How to solve this?

Thanks
 
J

John W. Vinson

I hv two tables as follow

Table1 Table2
FieldSex FieldSex FieldDes
---------- ---------- ---------
null null unknown
m m male
f f female

When i join this table in query those null record cannot be display.
Supposed null record will be displayed as unknown. How to solve this?
Click to expand...

Use some value other than NULL. NULL is not equal (or unequal!) to anything,
even itself. I'd suggest using something like u (for unknown). If it's really
important that the field appear blank, set the field's Allow Zero Length
Strings property to Yes, its Required property to Yes, and its DefaultValue
property to "" (an empty string).

John W. Vinson [MVP]
 
Z

zyus

I've changed the table setting but still no diff...the number of record
required are still out by the null field.

Want to follow your 1st option but is there any way i can patch the null
value to "U" more faster rather than doing it manually
 
J

John W. Vinson

I've changed the table setting but still no diff...the number of record
required are still out by the null field.
Click to expand...

What did you change? If the table still contains NULL then you're still stuck:
NULL *still* isn't equal to anything, and cannot be used in a join.
Want to follow your 1st option but is there any way i can patch the null
value to "U" more faster rather than doing it manually
Click to expand...

Run an Update query; use a criterion of "IS NULL" on the field, and update to
"U".

John W. Vinson [MVP]
 
I

i_takeuti

John W. Vinson said:
What did you change? If the table still contains NULL then you're still
stuck:
NULL *still* isn't equal to anything, and cannot be used in a join.


Run an Update query; use a criterion of "IS NULL" on the field, and update
to
"U".

John W. Vinson [MVP]
Click to expand...
 
Top