Create a Calculated Field from a list of criteria

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

Guest

I am trying to run a MakeTable Query in which I create a new Field called
[Region]. I wish for [Region] to have a value of "North", "South" or
"National". I have a list of around 10 Company Names in the [Company] field
for which I wish to assign to the "National" Region, otherwise the records
should be assigned to the "North" Region if they match 1 of 6 values in the
[Territory] field or to the "South" Region if they match any of the other
codes in the [Territory] field.

eg - Region:IIf([Company]=<<List of Companies to satisfy as TRUE>>,
"National, IIf([Territory] = <<list of Territories to satisfy as
TRUE>>,"North","South"))

How do I specify the list of Criteria for TRUE?

So far the only way that I have found is a recursive series of "IIf"
functions - which seems a bit clumsy.
 
paul@sofsol said:
I am trying to run a MakeTable Query in which I create a new Field
called [Region]. I wish for [Region] to have a value of "North",
"South" or "National". I have a list of around 10 Company Names in
the [Company] field for which I wish to assign to the "National"
Region, otherwise the records should be assigned to the "North"
Region if they match 1 of 6 values in the [Territory] field or to the
"South" Region if they match any of the other codes in the
[Territory] field.

eg - Region:IIf([Company]=<<List of Companies to satisfy as TRUE>>,
"National, IIf([Territory] = <<list of Territories to satisfy as
TRUE>>,"North","South"))

How do I specify the list of Criteria for TRUE?

So far the only way that I have found is a recursive series of "IIf"
functions - which seems a bit clumsy.
Click to expand...

If this is a one time deal, why not just add the field to the existing table
and do a manual update query?
It would probably be quicker than writing and testing code. It certainly
would be for at least the 10 national companies.
 
Could you use IIf([Company] in ("A","B","C")
"National, IIf([Territory] in ("a","b","c"),"North","South"))
 
Back
Top