Inner Join - Return "Unknown" if no match?

[treva26]

*SORRY I MEANT LEFT JOIN *

Probably a basic thing but I cant seem to think of the right keywords to search for it!

I have lookup table I use to return names for various "Sales Class" codes.
It all works good but if there is a code that isn't in the lookup table it leaves that field empty.
I want it to return the word "Unknown" for any code that doesn't have a match.

Here is the SQL:

SELECT [Data1].OrderNum, SalesClasses.[Name]
FROM [Data1] 
LEFT JOIN SalesClasses ON [Data1].[Sales Class] = SalesClasses.[Code1];

Table examples:

Data1:
OrderNum - Sales Class
111 - class1
222 - class2
333 - classX

SalesClasses:
Code1 - Name
class1 - Shoes
class2 - Boots

Desired Result:
OrderNum - Name
111 - Shoes
222 - Boots
333 - Unknown

 

[nanscombe]

I think the alteration below, using the Nz() function, should do you.

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

SELECT [Data1].OrderNum, [COLOR="Red"]Nz([/COLOR]SalesClasses.[Name][COLOR="red"], "Unknown") AS [Name][/COLOR]
FROM [Data1] 
LEFT JOIN SalesClasses ON [Data1].[Sales Class] = SalesClasses.[Code1];

If I don't do it I'm sure that someone will point out that "Name" is not good name for a field because it is a reserved word in Access.

This article lists words and symbols that you should not use in field, object, and variable names in Microsoft Access 2002 and later versions of Access because they are "reserved words." Reserved words have a specific meaning to Access or to the Microsoft Jet database engine. If you use a reserved word or symbol, you may receive an error such as the following:

 

[Galaxiom]

Another way to do it is with following as the Format property of the control where it is displayed in a form or report.

;;;"Unknown"

You can also colour it if you like:

;;;"Unknown"[red]

 

[treva26]

Perfect thanks Nigel!

And no I wouldn't really use Name as a fieldname