Two Address Fields from Two Tables in a Query

[Guest]

Hello,

I have a query with several tables. Two of the tables are called Encounter
and Encounter History. There are Admit Date fields in each of these tables,
but they are always different. Each table also has an Address field
(Encounter.Address and EncounterHistory.Address). What I want to do is show
one Address field in my query, depending on which table's Address field is
not null. There will never be an address filled in for both table's Address
fields. So I want to either show one or the other address, depending on
which field is not null, in a new field called "Address" in my query. Hope
this was clear and someone can help. Thanks in advance.

Janet

 

[Guest]

If there will never be an address filled in for both table's Address fields,
then you don't need to use the IIF to check if one of the fields is null,
just join the two fields

Select Field1, Field2, Encounter.Address & EncounterHistory.Address as
FullAddress From TableName .....

 

[Guest]

Thanks! You've been of great help to me today.

Janet

 

[Marshall Barton]

I have a query with several tables. Two of the tables are called Encounter
and Encounter History. There are Admit Date fields in each of these tables,
but they are always different. Each table also has an Address field
(Encounter.Address and EncounterHistory.Address). What I want to do is show
one Address field in my query, depending on which table's Address field is
not null. There will never be an address filled in for both table's Address
fields. So I want to either show one or the other address, depending on
which field is not null, in a new field called "Address" in my query.

As a side note to Ofer's solution. If there might be
addresses in both tables and you only wnat the history
address when the address in the encounter table is null, you
can do this:

Nz((Encounter.Address, EncounterHistory.Address)

 

[Guest]

Any time