three scinario iif

C

Carlee

Hi there,
I am having some trouble on what should be a rather easy solution...but for
the life of me..I am stumped.

I have a field called customer name. A customer can be:
An individual shown as (Last Name, First Name)
A Family, shown as (Last Name &"Family")
An Organization, shown as (Organization Name)

How do I write a nested iif in my query so that i can account for all three
variations on customer in one field? This will be used for an internal phone
list.

Many thanks,
Carlee
 
J

John Spencer (MVP)

Not quite sure what you are trying to do.

How can you (as a rule) tell which Customer Name is which type?

Individual has a comma in the name - but an organization could also have a comma.
Family has the word "Family" at the end.
Organization does not have the word Family - How about Family Services, Inc.?
WHOOPS that could be a family or an individual based on the rules so far defined.

In other words, there is no easy, reliable solution to this without another
field identifying the type of entity.
 
J

John Vinson

Hi there,
I am having some trouble on what should be a rather easy solution...but for
the life of me..I am stumped.

I have a field called customer name. A customer can be:
An individual shown as (Last Name, First Name)
A Family, shown as (Last Name &"Family")
An Organization, shown as (Organization Name)

How do I write a nested iif in my query so that i can account for all three
variations on customer in one field? This will be used for an internal phone
list.
Click to expand...

Use the Switch() function instead; it takes (essentially) any number
of arguments in pairs. When it's called, Access goes through the
arguments two at a time; if the first of the pair is TRUE it returns
the other member of that pair and quits. This lets you implement a
many-branch IF.

John W. Vinson[MVP]
 
C

Carlee

Hi John,
I should have been clearer. Yes, I have a distinction field. It is called
'Customer Type'. A customer can be an Individual, Family or Organization.
If it is an individual, then the field will have a last name and first. If
it is a family, it will have a family name and if it is an Organization, it
will have an organization name.
What I am trying to do is create a query for a report with one field, a
Customer field, that shows all three variations of customer.

Ideas?
 
J

John Vinson

Hi John,
I should have been clearer. Yes, I have a distinction field. It is called
'Customer Type'. A customer can be an Individual, Family or Organization.
If it is an individual, then the field will have a last name and first. If
it is a family, it will have a family name and if it is an Organization, it
will have an organization name.
What I am trying to do is create a query for a report with one field, a
Customer field, that shows all three variations of customer.

Ideas?
Click to expand...

ShowName: Switch(
[Customer Type] = "Individual", [Last Name] & ", " & [First Name],
[Customer Type] = "Family", [Last Name] & " Family",
[Customer Type] = "Organization", [Organization Name])


John W. Vinson[MVP]
 
Top