I Know This Should Be Simple, But....

  • Thread starter Thread starter lbrynes
  • Start date Start date
L

lbrynes

I have created several databases in my time using Access, and they ar
all very successful, however I have run into a problem that I know
simple expression will solve, yet i can't get the darn thing to work.
Here's the scenario,

I have a table with two different addresses, home and office. I hav
created the form using this table, and also a report based on the form
Within the form I have used a group option to define the mailin
address for a letter or label, however it is not working, so I hav
tried using the following:

=IIf(IsNull([Forms]![frmForm1]![Home Address 1] An
[Forms]![frmForm1]![Home Address 2]),[Forms]![frmForm1]![Address 1])
" " & IIf(IsNull([Forms]![frm1]![Address2]),
",[Forms]![frmForm1]![Address 2])

Hence, that isn't working either, because some of the records are no
of Null, and if they are it is still giving me only the address 2 a
the mailing address. What I am trying to essetially do is use th
radial button for the mailing option, go to the report and using
simple expression based on the option I have selected on the form prin
the damn label or letter with the correct address.

Any help is greatly appreciated as to what I am doing wrong..

feeling dreadfully inadequat
 
I would recommend saving the value of the option box into the table so that
you "remember" which address is the mailing address for the particular
contact. You could then use this value (1 or 2) in the IIf statement in the
report to print the correct address. Also, since you are using the address,
it shouldn't be Null; however, the 2nd line of the address may frequently be
Null. One of the easiest ways to get rid of this is to use the Can Shrink
property of the textbox. Another way is to use both + and & in your
concatenation. Null values will propagate and equation, so you can skip the
carriage return/line feed by connecting it to the Null using +.

Example:

=IIf([MailingAddress]=1, [HomeAddress1] & (Chr(13) & Chr(10)) +
[HomeAddress2] & Chr(13) & Chr(10) & [HomeCity] & ", " & [HomeState] & " " &
[HomeZip], [WorkAddress1] & (Chr(13) & Chr(10)) + [WorkAddress2] & Chr(13) &
Chr(10) & [WorkCity] & ", " & [WorkState] & " " & [WorkZip])

Chr(13) and Chr(10) can't be reversed or it won't work. Also, the field
being "plused" must be a text field. The example will remove the space
caused by the second line of the address if it doesn't exist. The
[MailingAddress]=1 refers to the value of the option group (1 or 2),
depending on which value you assigned to each address.

If you choose to put each line in its own textbox and use the CanShrink
property then the control source would be

=IIf([MailingAddress]=1, [HomeAddress1], [OfficeAddress1])

Change the fields for each textbox (i.e. each line of the address).

If [MailingAddress] is Null, you will get the Office Address as the mailing
address. Set a default value for Mailing Address to either 1 or 2 to avoid
Null values.

--
Wayne Morgan
Microsoft Access MVP


lbrynes said:
I have created several databases in my time using Access, and they are
all very successful, however I have run into a problem that I know a
simple expression will solve, yet i can't get the darn thing to work..
Here's the scenario,

I have a table with two different addresses, home and office. I have
created the form using this table, and also a report based on the form.
Within the form I have used a group option to define the mailing
address for a letter or label, however it is not working, so I have
tried using the following:

=IIf(IsNull([Forms]![frmForm1]![Home Address 1] And
[Forms]![frmForm1]![Home Address 2]),[Forms]![frmForm1]![Address 1]) &
" " & IIf(IsNull([Forms]![frm1]![Address2]),"
",[Forms]![frmForm1]![Address 2])

Hence, that isn't working either, because some of the records are not
of Null, and if they are it is still giving me only the address 2 as
the mailing address. What I am trying to essetially do is use the
radial button for the mailing option, go to the report and using a
simple expression based on the option I have selected on the form print
the damn label or letter with the correct address.

Any help is greatly appreciated as to what I am doing wrong..

feeling dreadfully inadequate
Click to expand...
 
I have worked around my issue. I have nested the IIf Statements t
select the records I wanted. This does not solve the issue with m
Option Group and the value relating to which being the default mailin
address. I have reworked the tables and forms so that the and optio
determines which address is home and which is a work address. O
well.. I'll keep you all updated, and if anyone else has an answer t
my initial dilemma, I'm still open to any and all advice.

T
 
Hi Wayne,

Thank you very much for your input and advice. I had already starte
that process, and have worked the statements in beautifully. M
initial problem was that the option really was not tied to anything.
The database knew it was there, but there was no "relation". What
duh... sometimes I think my head goes elsewhere when I am programming.

Laure
 
Back
Top