Join value from another Table to a Query

[shyjukj]

I need to join the various address field of a client and take the value to Invoice (Report). Customers PO Box, Address, City, Country & Tel number is joined together and is taken to report from single column. Here city and country value are numbers and the original value is in two other tables (namely Emirate_Master & Country_Master). The values are joined together before taking to report to avoid blank space if any value is Null)

When I join the values, everything comes right, except city and country. Instead of city and country name, the respective numbers come in the result.

The formula used in the query is given below;

AddressLine: 
(IIf([PO Box] Is Null
   ,""
   ,"PO Box # " & [PO Box] & ", ") & 
      (IIf([Address] Is Null
         ,""
         ,[Address] & ", ") & 
            (IIf([Emirate] Is Null
               ,""
               ,[Emirate] & ", ") & 
                  (IIf([Country/Region] Is Null
                     ,""
                     , [Country/Region])))))

[zmbd]

You have said nothing about your table structure.

Nether [Emirate] nor [Country/Region] can be a primary key if they ar null, or you have something very wrong in how you have set up your table relationships. That may your first issue as making a join between tables is easiest on the primary key. (> Database Normalization and Table Structures. )

You can ofcourse join on other fields and this is what you will need to do, is join on these fields back to the tables that have the text version then include the fields in the query with the text and use those instead of the numeric fields.

Also, all of this IIF() nastyness doesn't need to be done to avoid the extra spaces, read thru this article AND pay attention to the conversation about the propogation of NULLS:
What Is Null?
You'll find the method we use to joing things like:
[Name_first][Name_MI][Name_Last] when field [Name_MI] may be null without an extra space.