combine address fields 1

[THWatson]

Using Access 2003 (2000 format)

3 fields in tblTrinity are...
HouseNbr
Street
Address2

The "Address2" field is principally to be used for rural route addresses. In this case, the HouseNbr and Street fields are generally vacant. However it is possible for all 3 fields to have data.

What I would like to do is have a query column that looks at the data and makes the appropriate combination.

In cases where HouseNbr and Street have data, but no Address2, I would like to see:
12 Main St.

In cases where Address2 has data but no HouseNbr and Street, I would like to see:
R. R. #4

In cases where all 3 fields have data, I would like to see:
4598 District Road 8
R. R. #2

I have tried various IIf combinations, but can't get them all to work out properly.

Any suggestions. Perhaps a function would work best?

Tom

 

[dhookom]

Try

= HouseNbr & " " + Street & IIF(IsNull(HouseNbr), "",Chr(13) + Chr(10)) & Address2

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
This works fine for cases 2 and 3.

However, in case 1 an extra blank line is inserted. So that ends up as
12 Main St.
blank line

How's your grandson doing? I have my 8th grandchild on the way within this next month, and a Great grandchild coming in January. Makes a fella feel rather senior!

Tom

 

[dhookom]

=HouseNbr & " " + Street & IIF(IsNull(Address2+street), "",Chr(13) + Chr(10)) & Address2

Jackson (5) just entered the room full of energy. He wants to do something with Papa so I have to go ;-)


Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Eureka...that's got it!

Thanks.

Tom