Help with displaying correct address in a query

[Jacqueline]

In my Access 2000 mdb I am trying to create a report that
must choose the correct mailing address to send the
letter to...

The database holds 3 sets of mailing address for the
medical school applicants...Present...Permanent and Other

If I was using the present mailing address info in a
query it would be over 4 columns:

1. Address1

2. Display_Address2: IIf(IsNull([Address2]),[City] & ", "
& [Province],[Address2])

3. Display_Address3: IIf(IsNull([Address2]),[Postal_Code],
[City] & ", " & [Province])

4. Display_Address4: IIf(IsNull([Address2]),"",
[Postal_Code])

My problem is this...on that form there is also a
question which asks:
To which address do you want the final results sent:
Present...Permanent...Other (these are 3 checkboxes)

My ? is: if a student checks off they want their final
results sent to...say their Present address...how do I
display the correct address in the query that runs the
report?
I know it must be some kind of IIF statement that would
need to check which of the 3 boxes are checked...but with
the way the address is broken down in my above sample
address it seems a little confusing...

thanks for the help!

 

[Guest]

Thanks Rob...
I am trying to use what I have already...as these
checkboxes are chosen by the applicant on the online
Faculty of Medicine Admissions web site...
These chkbox values are 0 and -1 so should I do this for
each address line...

Address1: IIf([Send_Results_Pres]=-1,[Address1],IIf
([Send_Results_Perm]=-1,[Perm_Address1],[Other_Address1]))

I did this and it worked the 1st time...but the 2nd time
I ran it I got a circular reference caused by
Alias 'Address1 in query definition's Select list.

Here is the SQL view...
SELECT dbo_Users.User_Firstname, dbo_Users.User_Lastname,
IIf([Send_Results_Pres]=-1,[Address1],IIf
([Send_Results_Perm]=-1,[Perm_Address1],
[Other_Address1])) AS Address1
FROM dbo_Users INNER JOIN dbo_Personal ON
dbo_Users.User_ID = dbo_Personal.PersonalID;

-----Original Message-----
My first suggestion would be to use a combobox & list in

order to store which address the final results are to be
sent to. This way you could use the Choose function to
select the appropriate field (I think, I haven't tested
it). Also, you would not have to worry about there being
more than one checkbox marked.

That said, given what you have you could use something like this:

Addr1: IIF([Present],[Address1],IIF([Permanent], [PermAddress1],[OtherAddr1]))

Do this for each of your address lines (Address1,

Address2, City, State and ZIP) to get the raw information
you need, and then handle the "if Address2 is empty"
issue separately - either by using this first query as a
data source for a second query (which would use the logic
you already have), or by handling it at the report level.

Rob

----- Jacqueline wrote: -----

In my Access 2000 mdb I am trying to create a report that
must choose the correct mailing address to send the
letter to...

The database holds 3 sets of mailing address for the
medical school applicants...Present...Permanent and Other

If I was using the present mailing address info in a
query it would be over 4 columns:

1. Address1

2. Display_Address2: IIf(IsNull([Address2]),[City] & ", "
& [Province],[Address2])

3. Display_Address3: IIf(IsNull([Address2]), [Postal_Code],
[City] & ", " & [Province])

4. Display_Address4: IIf(IsNull([Address2]),"",
[Postal_Code])

My problem is this...on that form there is also a
question which asks:
To which address do you want the final results sent:
Present...Permanent...Other (these are 3 checkboxes)

My ? is: if a student checks off they want their final
results sent to...say their Present address...how do I
display the correct address in the query that runs the
report?
I know it must be some kind of IIF statement that would
need to check which of the 3 boxes are checked...but with
the way the address is broken down in my above sample
address it seems a little confusing...

thanks for the help!
.