Problem with IIF Statement

  • Thread starter Thread starter Confused
  • Start date Start date
C

Confused

Hi,

I currently have a query with a list of calls from a call
log database. Within this query i have LocationDesc and
Practice. I want to group the LocationDesc dependant on
the Practice. For example this is what I currently have in
my table:

Pratice LocationDesc
------------------------------------------------
National Practice Toronto (30010)
National Practice Toronto - Bay Street (30030)
National Shared Ser Toronto University (30018
Ragional Practice Regions
National Practice Calgary
National Practice Calgary (30620)

The table is much more extensive then this but this gives
a basic idea.

I put a column in my query next to LoctionDescr and named
it Geography. I then added a IFF function that reads:

Geography: IIf([Practice]="National Practice" And
[LocationDescr]="Calgary (30620)","Calgary",IIf([Practice]
="National Practice" And [LocationDescr]
="Calgary","Calgary",IIf([Practice]="National Practice"
And [LocationDescr]="Toronto (30010)","GTA",IIf([Practice]
="National Practice" And [LocationDescr]="Toronto - Bay
Street (30030)","GTA",IIf([Practice]="National Practice"
And [LocationDescr]="Toronto University (30018)","GTA",IIf
([Practice]="National Practice" And [LocationDescr]
="Vaughan (30016)","GTA",IIf([Practice]="National
Practice" And [LocationDescr]="Mississauga
(30013)","GTA",IIf([Practice]="National Practice" And
[LocationDescr]="National - 40 University
(30993)","GTA",IIf([Practice]="National Practice" And
[LocationDescr]="National (CCW) (30991)","GTA",IIf
([Practice]="National Practice" And [LocationDescr]
="National (University) (30990)","GTA",IIf([Practice]
="National Practice" And [LocationDescr]="North York
(30014)","GTA","No Data")))))))))))

This works fine, but I still have other groupings that I
want to do. However, when I add more, I get an
error "Sting cannot be longer then 255 charcters long".

Is there another way I can write this function or is there
any other way to perform this action. I have attempted to
put the function straight into Excel but Excel does not
allow more then 7 IF statements.

I will be using this as part of a macro, which pulls data
from the Access database to excel and performs a number of
grouping as above.

Any help is greatly appreciated.

Many thanks
 
You may be able to cut this down a little by using the
like expression.

IIf([Practice]="National Practice" And
[LocationDescr]like "Calgary*","Calgary",.....
Click to expand...

Or you could write a function and use if...else statements.
-----Original Message-----
Hi,

I currently have a query with a list of calls from a call
log database. Within this query i have LocationDesc and
Practice. I want to group the LocationDesc dependant on
the Practice. For example this is what I currently have in
my table:

Pratice LocationDesc
------------------------------------------------
National Practice Toronto (30010)
National Practice Toronto - Bay Street (30030)
National Shared Ser Toronto University (30018
Ragional Practice Regions
National Practice Calgary
National Practice Calgary (30620)

The table is much more extensive then this but this gives
a basic idea.

I put a column in my query next to LoctionDescr and named
it Geography. I then added a IFF function that reads:

Geography: IIf([Practice]="National Practice" And
[LocationDescr]="Calgary (30620)","Calgary",IIf([Practice]
="National Practice" And [LocationDescr]
="Calgary","Calgary",IIf([Practice]="National Practice"
And [LocationDescr]="Toronto (30010)","GTA",IIf([Practice]
="National Practice" And [LocationDescr]="Toronto - Bay
Street (30030)","GTA",IIf([Practice]="National Practice"
And [LocationDescr]="Toronto University (30018)","GTA",IIf
([Practice]="National Practice" And [LocationDescr]
="Vaughan (30016)","GTA",IIf([Practice]="National
Practice" And [LocationDescr]="Mississauga
(30013)","GTA",IIf([Practice]="National Practice" And
[LocationDescr]="National - 40 University
(30993)","GTA",IIf([Practice]="National Practice" And
[LocationDescr]="National (CCW) (30991)","GTA",IIf
([Practice]="National Practice" And [LocationDescr]
="National (University) (30990)","GTA",IIf([Practice]
="National Practice" And [LocationDescr]="North York
(30014)","GTA","No Data")))))))))))

This works fine, but I still have other groupings that I
want to do. However, when I add more, I get an
error "Sting cannot be longer then 255 charcters long".

Is there another way I can write this function or is there
any other way to perform this action. I have attempted to
put the function straight into Excel but Excel does not
allow more then 7 IF statements.

I will be using this as part of a macro, which pulls data
from the Access database to excel and performs a number of
grouping as above.

Any help is greatly appreciated.

Many thanks


.
Click to expand...
 
My suggestion would be to forget the IIF statements and
simply use a linked table at the query level. This would
be much easier to maintain in the long run.

Looks like you will need to create a new "Translate" table
with the following fields:

Practice LocationDesc Geography

The primary key would be both the Practice and
LocationDesc fields.

Populate it with the values as you've spelled them out in
your IIF statement, then add this table into your query,
linking by Practice and LoctionDesc. The query will then
automatically pull out the appropriate Geography value.

My other recommendation (if you don't already have it set
up this way) would be to create two additional tables to
hold your Practice and LocationDesc text string values and
assign a unique key (probably an AutoNumber) to each.
Then your data table would hold the key values instead of
strings, and things would run more efficiently.

If you decide to do this, you would want to do it before
creating the Translate table, as it would affect what is
stored in that tables key fields (strings vs. numbers).

HTH

Rob

-----Original Message-----
Hi,

I currently have a query with a list of calls from a call
log database. Within this query i have LocationDesc and
Practice. I want to group the LocationDesc dependant on
the Practice. For example this is what I currently have in
my table:

Pratice LocationDesc
------------------------------------------------
National Practice Toronto (30010)
National Practice Toronto - Bay Street (30030)
National Shared Ser Toronto University (30018
Ragional Practice Regions
National Practice Calgary
National Practice Calgary (30620)

The table is much more extensive then this but this gives
a basic idea.

I put a column in my query next to LoctionDescr and named
it Geography. I then added a IFF function that reads:

Geography: IIf([Practice]="National Practice" And
[LocationDescr]="Calgary (30620)","Calgary",IIf([Practice]
="National Practice" And [LocationDescr]
="Calgary","Calgary",IIf([Practice]="National Practice"
And [LocationDescr]="Toronto (30010)","GTA",IIf([Practice]
="National Practice" And [LocationDescr]="Toronto - Bay
Street (30030)","GTA",IIf([Practice]="National Practice"
And [LocationDescr]="Toronto University (30018)","GTA",IIf
([Practice]="National Practice" And [LocationDescr]
="Vaughan (30016)","GTA",IIf([Practice]="National
Practice" And [LocationDescr]="Mississauga
(30013)","GTA",IIf([Practice]="National Practice" And
[LocationDescr]="National - 40 University
(30993)","GTA",IIf([Practice]="National Practice" And
[LocationDescr]="National (CCW) (30991)","GTA",IIf
([Practice]="National Practice" And [LocationDescr]
="National (University) (30990)","GTA",IIf([Practice]
="National Practice" And [LocationDescr]="North York
(30014)","GTA","No Data")))))))))))

This works fine, but I still have other groupings that I
want to do. However, when I add more, I get an
error "Sting cannot be longer then 255 charcters long".

Is there another way I can write this function or is there
any other way to perform this action. I have attempted to
put the function straight into Excel but Excel does not
allow more then 7 IF statements.

I will be using this as part of a macro, which pulls data
from the Access database to excel and performs a number of
grouping as above.

Any help is greatly appreciated.

Many thanks


.
Click to expand...
 
Back
Top