three scinario iif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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]
 
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?
 
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]
 
Back
Top