Using iif to choose which address to display

[sjeskey]

I have an access table of alumni for my university,
and it stores both their home and their business address. there is
another field that is a dropdown box that you can choose which they
prefer to recieve their mail at.

now, i print out reports that basically makes labels out of them so i
can mail out to people.

im trying to write an iif statement, or somehow create reports that
will check the value in prefaddr (home or work), and then based on
that display all the corresponding address fields, either waddress,
wcity, wstate, and wzip, or haddress, hcity, hstate, and hzip.

i've tried many things and cant get it to work, im not sure if im
supposed to use a query to do that, then drive it into a report, or
just put it right into the report.

if anyone can help me with the expression i need to write i would
greatly appreciate it. i built the whole database and reports a few
years ago, (they are in access 2003) and just now was asked to fix
that problem. if it would be easier to do some vb backend, i could do
that, i just need a little guidance or a suggestion as to what the
easiest way to go about it would be, as im far from an access
programmer ( i do vb/asp.net and c#, so thinking back to my access
days is difficult)

 

[John Spencer]

One method would be to concatenate the address into one field in a query
using the following.

IIF(PrefAddr = "Home",
haddress & Chr(13) & Chr(10) & hcity & ", " & hstate & " " & hzip,
waddress & Chr(13) & Chr(10) & wcity & ", " & wstate & " " & wzip)

Another would be to use the Format event of the report's detail event to
select the address and populate one or multiple unbound controls.

I prefer the query method but either should give you the results you want.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jeff Boyce]

This may be a bit of a kludge...

One approach might be along the lines of:

AddrStr: IIF([prefaddr]="work",[waddr] & Chr(10) & Chr(13) & [wcity] &
", " & [wstate] ...

The Chr(10) & Chr(13) adds a carriage return and line feed. You would
basically concatenate a string of the values in the address, based on the
combobox.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[sjeskey]

i realized the field names i had in are wrong, so i tried to fix them
to the proper ones, and when i try to run the query, it asks me to
input a value (which it shouldnt)

IIf("Contact Location"="Home","Home Address" & Chr(13) & Chr(10) &
"City 2" & ", " & "State 2" & " " & "Zip 2","Work Address" & Chr(13) &
Chr(10) & "City" & ", " & "State" & " " & "Zip")

is it a problem that that there are spaces in the names? i made sure
all the cases are properly done, so everything is capitolized..
should i just put this into a blank query by itself, or do i need to
include all the different fields im using in the query?

 

[sjeskey]

i tried putting the iif statement into the top box (field) in the
query,
and this is what it spit back for me for the entire page



"Work Address
City, State Zip"

with those actual words, not the fields from the table.


any ideas?

 

[sjeskey]

bump