By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,666 Members | 1,909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,666 IT Pros & Developers. It's quick & easy.

What's the 'best' was to print a complex calculated field on a report?

P: n/a
I'm printing address labels in a membership database. The rules are
that if both MailingAddress1 and MailingAddress2 are Null, then use
home address, otherwise use mailing address. If address line 1 is
null, don't show a blank line. That sounds simple, and a long IIf
function in the report's query does the job, but it's getting a bit
too long and horrible for me. It may be unreasonable, but I just don't
like big IIfs. Apart from anything else, they are not easy to
maintain.

It gets even bigger, because of family memberships where family
members use the parent address.

It's at the end of this post for those with a masochistic bent. And
that's before I add the stuff to make sure that the parent has an
address ... Uggh.

I could use the Format event to compose the address, but then I'd have
to include all the address fields on the report (Visible = False) and,
apart from anything else, it's kind of difficult to fit 17 extra
fields onto a label-sized report.

I could use a GetMailingAddress() function in the report's query, but
they tend to be slow.

I could create a temp table of mailing addresses in the OnOpen event.

I could, er ...,

What do you think?
MailingAddress:
IIf([tblMember.UseParentAddress],IIf(IsNull([tblParentMember.MailingAddress1])
And
IsNull([tblParentMember.MailingAddress2]),IIf(IsNull([tblParentMember.HomeAddress1]),[tblParentMember.HomeAddress2]
& Chr(13) & Chr(10) & [tblParentMember.HomeCity] & " " &
[tblParentMember.HomeState] & " " &
[tblParentMember.HomePostCode],[tblParentMember.HomeAddress1] &
Chr(13) & Chr(10) & [tblParentMember.HomeAddress2] & Chr(13) & Chr(10)
& [tblParentMember.HomeCity] & " " & [tblParentMember.HomeState] & "
" &
[tblParentMember.HomePostCode]),IIf(IsNull([tblParentMember.MailingAddress1]),[tblParentMember.MailingAddress2]
& Chr(13) & Chr(10) & [tblParentMember.MailingCity] & " " &
[tblParentMember.MailingState] & " " &
[tblParentMember.MailingPostCode],[tblParentMember.MailingAddress1] &
Chr(13) & Chr(10) & [tblParentMember.MailingAddress2] & Chr(13) &
Chr(10) & [tblParentMember.MailingCity] & " " &
[tblParentMember.MailingState] & " " &
[tblParentMember.MailingPostCode])),IIf(IsNull([tblMember.MailingAddress1])
And
IsNull([tblMember.MailingAddress2]),IIf(IsNull([tblMember.HomeAddress1]),[tblMember.HomeAddress2]
& Chr(13) & Chr(10) & [tblMember.HomeCity] & " " &
[tblMember.HomeState] & " " &
[tblMember.HomePostCode],[tblMember.HomeAddress1] & Chr(13) & Chr(10)
& [tblMember.HomeAddress2] & Chr(13) & Chr(10) & [tblMember.HomeCity]
& " " & [tblMember.HomeState] & " " &
[tblMember.HomePostCode]),IIf(IsNull([tblMember.MailingAddress1]),[tblMember.MailingAddress2]
& Chr(13) & Chr(10) & [tblMember.MailingCity] & " " &
[tblMember.MailingState] & " " &
[tblMember.MailingPostCode],[tblMember.MailingAddress1] & Chr(13) &
Chr(10) & [tblMember.MailingAddress2] & Chr(13) & Chr(10) &
[tblMember.MailingCity] & " " & [tblMember.MailingState] & " " &
[tblMember.MailingPostCode])))

--
Regards.
Richard.
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.