Help! Code for use Mail1 if present, else use regular Street, etc

[Guest]

Hello -

I have a table with essentially FirstName, LastName, HouseNo, StreetDirection, Street, StRdAveDr, City, State, Zip, Zip4, Mail1, Mail2, MailCity, MailState, MailZip, Mail4. [I realize this is designed poorly but it will only be used for a short time and then scrapped.]

I need a query that goes something like this: If Mail1, Mail2, MailCity, MailState ... Is Not Null then use that address; else use the residential address.

I also need to mail only one item to each house (e.g. if two or more people reside at the same address, they can share the letter).

Any suggestions will be greatly appreciated!

 

[Michel Walsh]

Hi,


SELECT Nz( Mail1 + chr(13) + Mail2 + chr(13) + MailCIty + chr(13) +
MailState,
Street & chr(13) & StrRdAceDr & char(13) & CIty & chr(13) & State
& Zip )

FROM myTable






Hoping it may help,
Vanderghast, Access MVP

Hello -

I have a table with essentially FirstName, LastName, HouseNo,

StreetDirection, Street, StRdAveDr, City, State, Zip, Zip4, Mail1, Mail2,
MailCity, MailState, MailZip, Mail4. [I realize this is designed poorly but
it will only be used for a short time and then scrapped.]

I need a query that goes something like this: If Mail1, Mail2, MailCity,

MailState ... Is Not Null then use that address; else use the residential
address.

I also need to mail only one item to each house (e.g. if two or more

people reside at the same address, they can share the letter).

 

[Guest]

Michel -

That's a brilliant little piece of code!

I did forget to mention, however, that I need it in a format so I can put it in Excel for labels; that is, I need all of the street address stuff on one line and the city, state and zip on the next line. Is there any way this can be modified to do same, or am I asking for way too much of Access?

P.S. I am not sure how this MSDN site works. I wanted to click on the button in the email I received saying you answered my question, but I was afraid if I did, you may not get notified I have this additional question. Post me back whether you have an additional answer or not and I'll be sure to click the button so you get credit!

Thanks so much!

--
Sandy

Hi,


SELECT Nz( Mail1 + chr(13) + Mail2 + chr(13) + MailCIty + chr(13) +
MailState,
Street & chr(13) & StrRdAceDr & char(13) & CIty & chr(13) & State
& Zip )

FROM myTable






Hoping it may help,
Vanderghast, Access MVP

Hello -

I have a table with essentially FirstName, LastName, HouseNo,

StreetDirection, Street, StRdAveDr, City, State, Zip, Zip4, Mail1, Mail2,
MailCity, MailState, MailZip, Mail4. [I realize this is designed poorly but
it will only be used for a short time and then scrapped.]

I need a query that goes something like this: If Mail1, Mail2, MailCity,

MailState ... Is Not Null then use that address; else use the residential
address.

I also need to mail only one item to each house (e.g. if two or more

people reside at the same address, they can share the letter).

Any suggestions will be greatly appreciated!

 

[Michel Walsh]

Hi,


You can try CHR(13) + CHR(10) for a newline-linefeed, so the result
would appear on two lines, but in one record (one cell).:


SELECT Nz( Mail1 + " " + Mail2 + chr(13) + chr(10) + MailCity +
" " + MailState,
Street & " " & StrRdAceDrive & Chr(13) & Chr(10) & City
& " " & State & " " & Zip )

FROM myTable


If you want two records, I would try:


SELECT Nz(Mail1 + " " + Mail2 , Street & " " & StrRdAceDrive ) as
Content,
Nz(Mail1 + " " + Mail2 + " " + MailCity + " " + MailState ,
Street & " " & StrRdAceDrive & " " & City & " " & State & " " & Zip )
as TempKey, 1 As lineNumber
FROM myTable

UNION ALL

SELECT Nz(MailCity + " " + MailState, City & " " & State & " " &
Zip ),
Nz(Mail1 + " " + Mail2 + " " + MailCity + " " + MailState ,
Street & " " & StrRdAceDrive & " " & City & " " & State & " " &
Zip ), 2
FROM myTable

ORDER BY TempKey, LineNumber



and you would have to somehow "hide" the last two columns (used to ORDER BY
correctly)



I am not registered with the MSDN site, there is not need to "get the
credit"




Hoping it may help,
Vanderghast, Access MVP

Michel -

That's a brilliant little piece of code!

I did forget to mention, however, that I need it in a format so I can put

it in Excel for labels; that is, I need all of the street address stuff on
one line and the city, state and zip on the next line. Is there any way
this can be modified to do same, or am I asking for way too much of Access?

P.S. I am not sure how this MSDN site works. I wanted to click on the

button in the email I received saying you answered my question, but I was
afraid if I did, you may not get notified I have this additional question.
Post me back whether you have an additional answer or not and I'll be sure
to click the button so you get credit!

Thanks so much!

--
Sandy

Hi,


SELECT Nz( Mail1 + chr(13) + Mail2 + chr(13) + MailCIty + chr(13) +
MailState,
Street & chr(13) & StrRdAceDr & char(13) & CIty & chr(13) & State
& Zip )

FROM myTable






Hoping it may help,
Vanderghast, Access MVP

Hello -

I have a table with essentially FirstName, LastName, HouseNo,

StreetDirection, Street, StRdAveDr, City, State, Zip, Zip4, Mail1, Mail2,
MailCity, MailState, MailZip, Mail4. [I realize this is designed poorly but
it will only be used for a short time and then scrapped.]

I need a query that goes something like this: If Mail1, Mail2,

MailCity,
MailState ... Is Not Null then use that address; else use the residential
address.

I also need to mail only one item to each house (e.g. if two or more

people reside at the same address, they can share the letter).

Any suggestions will be greatly appreciated!