Choosing One Set of Address

[Babs3who]

This is a little tricky to explain so I will do my best.

Where I work we utilize our database to mail out 2,000+ newsletters every
month. We recently cleaned up our database and want to make it more effective
and efficient for all of us.

In our master table, we have home addresses and business addresses for each
record, if applicable. We want to print home addresses by default if there is
no business address, business addresses if there is no home address, and to
NOT print the home address if there is a business address available for that
record.

If that made any sense, how can I set up our Access 2003 to do that? I would
appreciate any feedback.

 

[bhicks11 via AccessMonster.com]

What are you using to run the process? Is it a query or vba?

You can use the IIF() function to ascertain if the home address is null, etc.


Something like this in the query field:

address: IIF(isnull(

.[homeaddress]),

.[businessaddress],

.
[homeaddress])

Bonnie
http://www.dataplus-svc.com

 

[Duane Hookom]

You can use the Nz() function like:
=Nz([Business Address], [Home Address])

 

[bhicks11 via AccessMonster.com]

Sorry Duane, I don't get it. NZ() only converts a null value to zero?

Bonnie
http://www.dataplus-svc.com

You can use the Nz() function like:
=Nz([Business Address], [Home Address])

This is a little tricky to explain so I will do my best.

[quoted text clipped - 10 lines]

If that made any sense, how can I set up our Access 2003 to do that? I would
appreciate any feedback.

 

[John Spencer (MVP)]

NZ converts a null to whatever the second argument is. If you fail to specify
the second argument, NZ will return zero for numeric fields, but a zero-length
string for text fields.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Sorry Duane, I don't get it. NZ() only converts a null value to zero?

Bonnie
http://www.dataplus-svc.com

You can use the Nz() function like:
=Nz([Business Address], [Home Address])

This is a little tricky to explain so I will do my best.

[quoted text clipped - 10 lines]

If that made any sense, how can I set up our Access 2003 to do that? I would
appreciate any feedback.

 

[bhicks11 via AccessMonster.com]

Thank you John, didn't know that. I searched in Help before commenting for a
more complete understanding of NZ and it took me to a broken link.

Bonnie
http://www.dataplus-svc.com

NZ converts a null to whatever the second argument is. If you fail to specify
the second argument, NZ will return zero for numeric fields, but a zero-length
string for text fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Sorry Duane, I don't get it. NZ() only converts a null value to zero?

[quoted text clipped - 9 lines]