Null value in a lookup table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:
I have a lookup table which contains a null value and its description Like
Field1 FieldDesc
CA California
Unknown
WA Washington
However, when I do a query using this lookup table, the record with null
value will not show up. Is there any way to fix this problem?
Thanks in advance!
 
I am assuming that you're joining a table to your lookup table in the query.
Assuming that the answer is yes, a Null value in a joining field will
eliminate that record from being selected .. this is because nothing is ever
equal to Null, including Null (Null is not equal to Null).

You'll need to use a unique value that substitutes for Null in your lookup
table, and then the query will need to use an expression that converts a
Null to this unique value in the join clause.

Post some more info about what your data are and your query is, and we
should be able to make a suggestion for you.
 
Hi Ken:
Your guess is correct. I have two tables, main table and lookup table. The
main table holds all geographic information, but some records in this table
have nothing in the state field, like
Client State
1 CA
2
3 WA
I use the lookup table (as below) in a query to provide the full name of all
states. If the state field in the main table is empty, I treat it as unknown
and I got trouble to get the correct result from the query. By the way, I do
not want to change the main table.
Thanks!
 
You could pick an abbreviation you know won't be used, and then use the Nz()
function in your lookup. Rather than ON Table1.State = Table2.Field1, you'd
use ON Nz(Table1.State, "XX") = Nz(Table2.Field1, "XX")

You won't be able to do this on the graphical interface: you'll have to go
into the SQL view and manually change the SQL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Thanks for your suggestion!


Douglas J. Steele said:
You could pick an abbreviation you know won't be used, and then use the Nz()
function in your lookup. Rather than ON Table1.State = Table2.Field1, you'd
use ON Nz(Table1.State, "XX") = Nz(Table2.Field1, "XX")

You won't be able to do this on the graphical interface: you'll have to go
into the SQL view and manually change the SQL.
Click to expand...
 
Back
Top