whew, this thread was quite a bundle to get thru. please read the following
book <g> and hopefully it will help.
first, let's talk about the IIf() function for a minute. remember that the
IIf() function syntax (in English) is:
IIf(this expression is true, then return this, otherwise return this)
*the "otherwise return this" portion is not directly required, but it's
better not to leave it out of the IIf() function* because an unaddressed
situation will cause the function to return nothing.
a (simple) nested IIf() function syntax is either:
IIf(this expression is true, then IIf(this expression is true, return this,
otherwise return this), otherwise return this)
or
IIf(this expression is true, then return this, otherwise IIf(this expression
is true, return this, otherwise return this))
your code from the previous post says, in more-or-less English:
IIf(the MailCode = "H" is true,
then return the Home_address1,
*otherwise-return-this is left out*)
& concatenate the above return to the return of the following statement:
IIf(the MailCode = "A" is true Or the FirmPOBox Is Null is true,
then return FirmAddress1 & concatenate a space & concatenate
FirmAddress2,
otherwise return FirmPOBox.)
reading it as more-or-less English, you can see why you're getting the
results you stated below. your IIf statements and explanations in previous
posts seem somewhat contradictory and incomplete. let me see if i can
correctly state what you're actually trying to do:
if the mail code is "A":
return the firm's PO Box
but if the PO Box is blank, return the firm's Address1 and a space
and Address2
if the mail code is "H":
return the Home Address
but if the Home Address is blank, return the firm's Address1 and a
space and Address2
if the mail code is blank:
follow the same instructions as when the mail code is "A"
is that correct? if *not*, post corrections. if it is correct, try this:
IIf([Member File]![MailCode] = "A" Or [Member File]![MailCode] Is Null,
IIf([masterFIRMinfo]![FirmPOBox] Is Null, [masterFIRMinfo]![FirmAddress1] &
" " & [masterFIRMinfo]![FirmAddress2], [masterFIRMinfo]![FirmPOBox]),
IIf([Member File]![MailCode] = "B", IIf([Member File]![Home_Address1] Is
Null, [masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2], [Member File]![Home_Address1])))
in more-or-less English:
IIf(the MailCode = "A" is true Or the Mail Code Is Null is true,
then IIf( the FirmPOBox Is Null,
then return FirmAddress1 and " " and FirmAddress2,
otherwise return FirmPOBox)
otherwise IIf(the MailCode = "B",
then IIf(the Home_Address1 Is Null,
then return FirmAddress1 and " " and
FirmAddress2,
otherwise return Home_Address1),
*otherwise-do-this* is left out)))
make sure you include all three parens ))) at the end.
you don't seem to address instances where the mail code is neither A nor B
nor blank. (that's why the final "otherwise-do-this" is left out of the code
above.) are A, B and Null the only possible values in the mail code field?
if there are other possible values, you need to include the "otherwise, do
this" portion of the IIf() function to handle those values.
that's the nested-IIf-statement-from-hell, for sure, but i couldn't come up
with a more succinct expression. i would probably write a VBA function to
process it all, passing all the necessary field parameters to the function.
it would certainly be easier to write, debug and maintain in VBA.
hth
Rebecca said:
I now have this:
Address: IIf([Member File]![MailCode]="H",[Member file]![Home_address1]) &
IIf([Member File]![MailCode]="A" Or [masterFIRMinfo]![FirmPOBox] Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[masterFIRMinfo]![FirmPOBox])
And it finally is showing the "H" address but it also shows the Firm Address
right beside it. What I need is if there is an "H" (in the mailcode) I
need only [Home_address1] and if it is blank the address from
[FirmAddress1] and if there is an "A" or [FirmPOBox] is null, I need the
[FirmAddress1]
If I put an OR in replace of the & before the second IIF, I get a result
of -1 in the field.
I'm not sure if I am any closer to solving this or not.. Any thoughts would
be greatly appreciated!
Rebecca S.
Rebecca said:
I think my problem is I don't understand SQL and I think that is what
I
need
in order for me to get the results I need in my query.
This is what I have in the design view, field row: (running Access XP)
Address: IIf([Member File]![MailCode]="A" Or
[masterFIRMinfo]![FirmPOBox]
Is
Null,[masterFIRMinfo]![FirmAddress1] & " " &
[masterFIRMinfo]![FirmAddress2],[masterFIRMinfo]![FirmPOBox]) And
IIf([Member File]![MailCode]="H",[Member File]![Home_Address1],[Member
File]![Home_city], [Member File]![Home_State], [Member File]![Home_Zip])
Of course it doesn't work. My guess is having the two IIF statements like
that is my problem. What I need is both IIF statements combined and produce
the information that I need without running two separate queries (that is
how I handle it now) I run a lot of word merges off of this query.
Thanks in advance!!!!
Rebecca S.
.. computers!..