IIf Statement Criteria Problem

  • Thread starter Thread starter gillah11
  • Start date Start date
G

gillah11

My first iif statement reads as follows:

Volume: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO",[policy_id ])

and works well. Now I want to build a field called "Usage" with the same
criteria as above and additional criteria as follows:

[Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.

I've added the additonal criteria six different ways to Sunday, and the
result I get each time includes items in the States that I've already
filtered out.

Ticket_Created_Date Volume Usage State_Cd
7/7/2009 2312638 FL
7/7/2009 3067181 SC
7/7/2009 3033630 3033630 IN
7/7/2009 3121385 3121385 TX
7/7/2009 3154413 3154413 CO
7/7/2009 3225028 3225028 MN
7/7/2009 3241041 3241041 MN
7/7/2009 3364325 3364325 AZ
 
Are you saying that you wish to add those additional criteria IN PLACE OF
the others already mentioned, or IN ADDITION TO? Since you didn't include
HOW you are trying to add those, I can only guess that you've told Access to
look for anything that matches the first set of criteria, OR anything that
matches the second set.

It almost seems like you could create a query that returns ONLY the records
that meet those criteria, rather than try to do so via an IIF() statement.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The additional criteria in addition to the original criteria. I've told
access to look for anything that matches the first criteria and additional
criterea in the following ways, none of which give the correct result:

Try #1 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and [Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.
,[policy_id ])

Try #2 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and [Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1
,[policy_id ]).

etc... I think you get the idea. The rest were other variations of this
over and over. Where the criteria set for "Volume" works, any of the
criteria set I've tried for "Usage" will give me any policies where
[Discount_Flag ]=1 Or[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1, regardless of the state critera within.

Thanks




Jeff Boyce said:
Are you saying that you wish to add those additional criteria IN PLACE OF
the others already mentioned, or IN ADDITION TO? Since you didn't include
HOW you are trying to add those, I can only guess that you've told Access to
look for anything that matches the first set of criteria, OR anything that
matches the second set.

It almost seems like you could create a query that returns ONLY the records
that meet those criteria, rather than try to do so via an IIF() statement.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

gillah11 said:
My first iif statement reads as follows:

Volume: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO",[policy_id ])

and works well. Now I want to build a field called "Usage" with the same
criteria as above and additional criteria as follows:

[Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.

I've added the additonal criteria six different ways to Sunday, and the
result I get each time includes items in the States that I've already
filtered out.

Ticket_Created_Date Volume Usage State_Cd
7/7/2009 2312638 FL
7/7/2009 3067181 SC
7/7/2009 3033630 3033630 IN
7/7/2009 3121385 3121385 TX
7/7/2009 3154413 3154413 CO
7/7/2009 3225028 3225028 MN
7/7/2009 3241041 3241041 MN
7/7/2009 3364325 3364325 AZ
Click to expand...
Click to expand...
 
I'll suggest again that you see if you can use a query to select only those
records that match your (multiple) criteria.

Regards

Jeff Boyce
Microsoft Office/Access MVP

gillah11 said:
The additional criteria in addition to the original criteria. I've told
access to look for anything that matches the first criteria and additional
criterea in the following ways, none of which give the correct result:

Try #1 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and
[Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.
,[policy_id ])

Try #2 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and
[Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1
,[policy_id ]).

etc... I think you get the idea. The rest were other variations of this
over and over. Where the criteria set for "Volume" works, any of the
criteria set I've tried for "Usage" will give me any policies where
[Discount_Flag ]=1 Or[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1, regardless of the state critera within.

Thanks




Jeff Boyce said:
Are you saying that you wish to add those additional criteria IN PLACE OF
the others already mentioned, or IN ADDITION TO? Since you didn't
include
HOW you are trying to add those, I can only guess that you've told Access
to
look for anything that matches the first set of criteria, OR anything
that
matches the second set.

It almost seems like you could create a query that returns ONLY the
records
that meet those criteria, rather than try to do so via an IIF()
statement.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

gillah11 said:
My first iif statement reads as follows:

Volume: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO",[policy_id ])

and works well. Now I want to build a field called "Usage" with the
same
criteria as above and additional criteria as follows:

[Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.

I've added the additonal criteria six different ways to Sunday, and the
result I get each time includes items in the States that I've already
filtered out.

Ticket_Created_Date Volume Usage State_Cd
7/7/2009 2312638 FL
7/7/2009 3067181 SC
7/7/2009 3033630 3033630 IN
7/7/2009 3121385 3121385 TX
7/7/2009 3154413 3154413 CO
7/7/2009 3225028 3225028 MN
7/7/2009 3241041 3241041 MN
7/7/2009 3364325 3364325 AZ
Click to expand...
Click to expand...
Click to expand...
 
OK, I think I've determined that in the "Usage" criteria, I'm not
compensating at all for the "other" states that I DON'T want to include.
Where in the "Volume" statement I told the criteria ONLY what I wanted, I'm
now telling the "Usage" criteria that I want those AND the additional items.
So for instance I need it to give me ONLY the critia in the "Volume" category
and for those items, tell me which match the additional criteria. The
additonal states I'm getting right now aren't being vetted out of the "Usage"
criteria. I would want to say this, this and this, BUT NOT this....
for instance. How do I do that? Is there a way to state [state_cd] NOT IN
("FL".....

I can't find "not in" but want the opposite of "in'...

gillah11 said:
The additional criteria in addition to the original criteria. I've told
access to look for anything that matches the first criteria and additional
criterea in the following ways, none of which give the correct result:

Try #1 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and [Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.
,[policy_id ])

Try #2 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and [Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1
,[policy_id ]).

etc... I think you get the idea. The rest were other variations of this
over and over. Where the criteria set for "Volume" works, any of the
criteria set I've tried for "Usage" will give me any policies where
[Discount_Flag ]=1 Or[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1, regardless of the state critera within.

Thanks




Jeff Boyce said:
Are you saying that you wish to add those additional criteria IN PLACE OF
the others already mentioned, or IN ADDITION TO? Since you didn't include
HOW you are trying to add those, I can only guess that you've told Access to
look for anything that matches the first set of criteria, OR anything that
matches the second set.

It almost seems like you could create a query that returns ONLY the records
that meet those criteria, rather than try to do so via an IIF() statement.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

gillah11 said:
My first iif statement reads as follows:

Volume: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO",[policy_id ])

and works well. Now I want to build a field called "Usage" with the same
criteria as above and additional criteria as follows:

[Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.

I've added the additonal criteria six different ways to Sunday, and the
result I get each time includes items in the States that I've already
filtered out.

Ticket_Created_Date Volume Usage State_Cd
7/7/2009 2312638 FL
7/7/2009 3067181 SC
7/7/2009 3033630 3033630 IN
7/7/2009 3121385 3121385 TX
7/7/2009 3154413 3154413 CO
7/7/2009 3225028 3225028 MN
7/7/2009 3241041 3241041 MN
7/7/2009 3364325 3364325 AZ
Click to expand...
Click to expand...
Click to expand...
 
Thank you, I understand your suggestion, but what I don't understand is how
that isn't what I'm already trying to do.

I apologize if I've not given enough information nor made the information
I've given clear enough, but I am trying to selet only those that match the
multiple criteria.

Jeff Boyce said:
I'll suggest again that you see if you can use a query to select only those
records that match your (multiple) criteria.

Regards

Jeff Boyce
Microsoft Office/Access MVP

gillah11 said:
The additional criteria in addition to the original criteria. I've told
access to look for anything that matches the first criteria and additional
criterea in the following ways, none of which give the correct result:

Try #1 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and
[Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.
,[policy_id ])

Try #2 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and
[Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1
,[policy_id ]).

etc... I think you get the idea. The rest were other variations of this
over and over. Where the criteria set for "Volume" works, any of the
criteria set I've tried for "Usage" will give me any policies where
[Discount_Flag ]=1 Or[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1, regardless of the state critera within.

Thanks




Jeff Boyce said:
Are you saying that you wish to add those additional criteria IN PLACE OF
the others already mentioned, or IN ADDITION TO? Since you didn't
include
HOW you are trying to add those, I can only guess that you've told Access
to
look for anything that matches the first set of criteria, OR anything
that
matches the second set.

It almost seems like you could create a query that returns ONLY the
records
that meet those criteria, rather than try to do so via an IIF()
statement.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

My first iif statement reads as follows:

Volume: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO",[policy_id ])

and works well. Now I want to build a field called "Usage" with the
same
criteria as above and additional criteria as follows:

[Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.

I've added the additonal criteria six different ways to Sunday, and the
result I get each time includes items in the States that I've already
filtered out.

Ticket_Created_Date Volume Usage State_Cd
7/7/2009 2312638 FL
7/7/2009 3067181 SC
7/7/2009 3033630 3033630 IN
7/7/2009 3121385 3121385 TX
7/7/2009 3154413 3154413 CO
7/7/2009 3225028 3225028 MN
7/7/2009 3241041 3241041 MN
7/7/2009 3364325 3364325 AZ
Click to expand...
Click to expand...
Click to expand...
 
Well I determined that if I use the "Volume" criteria in addition to the
addtional criteria in an iif statement in the field row of the query design
and THEN also add addtional criteria to exclude the state_cd's that aren't
covered by any of the other criteria I get the final result I wanted. So in
the criteria row I added:

[State_cd ]<>"FL" And [State_cd ]<>"SC"

It sure seems like the long way around the answer. Do you think there is
any easier way to write this?

gillah11 said:
Thank you, I understand your suggestion, but what I don't understand is how
that isn't what I'm already trying to do.

I apologize if I've not given enough information nor made the information
I've given clear enough, but I am trying to selet only those that match the
multiple criteria.

Jeff Boyce said:
I'll suggest again that you see if you can use a query to select only those
records that match your (multiple) criteria.

Regards

Jeff Boyce
Microsoft Office/Access MVP

gillah11 said:
The additional criteria in addition to the original criteria. I've told
access to look for anything that matches the first criteria and additional
criterea in the following ways, none of which give the correct result:

Try #1 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and
[Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.
,[policy_id ])

Try #2 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and
[Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1
,[policy_id ]).

etc... I think you get the idea. The rest were other variations of this
over and over. Where the criteria set for "Volume" works, any of the
criteria set I've tried for "Usage" will give me any policies where
[Discount_Flag ]=1 Or[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1, regardless of the state critera within.

Thanks




:

Are you saying that you wish to add those additional criteria IN PLACE OF
the others already mentioned, or IN ADDITION TO? Since you didn't
include
HOW you are trying to add those, I can only guess that you've told Access
to
look for anything that matches the first set of criteria, OR anything
that
matches the second set.

It almost seems like you could create a query that returns ONLY the
records
that meet those criteria, rather than try to do so via an IIF()
statement.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

My first iif statement reads as follows:

Volume: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO",[policy_id ])

and works well. Now I want to build a field called "Usage" with the
same
criteria as above and additional criteria as follows:

[Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.

I've added the additonal criteria six different ways to Sunday, and the
result I get each time includes items in the States that I've already
filtered out.

Ticket_Created_Date Volume Usage State_Cd
7/7/2009 2312638 FL
7/7/2009 3067181 SC
7/7/2009 3033630 3033630 IN
7/7/2009 3121385 3121385 TX
7/7/2009 3154413 3154413 CO
7/7/2009 3225028 3225028 MN
7/7/2009 3241041 3241041 MN
7/7/2009 3364325 3364325 AZ
Click to expand...
Click to expand...
Click to expand...
 
The following is untested aircode:

SELECT X, Y, Z
FROM YourTable
WHERE (([Ticket_create_date]>#4/13/2009#) And ([State_cd] In
("IL","AL","MI","OR","MS","KY")) And
([Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1) Or
(([Ticket_create_date]>#5/7/2009#) And ([State_cd] In
("TX","MO","WI")) And
([Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1) Or
....

I believe the difference is the parentheses ... in the version you tried
below, you didn't include all of the ...Flag possibilities within your
"AND", thus allowing either your initial conditions OR one of your flag
conditions.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



gillah11 said:
Well I determined that if I use the "Volume" criteria in addition to the
addtional criteria in an iif statement in the field row of the query
design
and THEN also add addtional criteria to exclude the state_cd's that aren't
covered by any of the other criteria I get the final result I wanted. So
in
the criteria row I added:

[State_cd ]<>"FL" And [State_cd ]<>"SC"

It sure seems like the long way around the answer. Do you think there is
any easier way to write this?

gillah11 said:
Thank you, I understand your suggestion, but what I don't understand is
how
that isn't what I'm already trying to do.

I apologize if I've not given enough information nor made the information
I've given clear enough, but I am trying to selet only those that match
the
multiple criteria.

Jeff Boyce said:
I'll suggest again that you see if you can use a query to select only
those
records that match your (multiple) criteria.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The additional criteria in addition to the original criteria. I've
told
access to look for anything that matches the first criteria and
additional
criterea in the following ways, none of which give the correct
result:

Try #1 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd]
In
("IL","AL","MI","OR","MS","KY") Or [Ticket_created_date]>#5/7/2009#
And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009#
And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and
[Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.
,[policy_id ])

Try #2 - Usage: IIf([Ticket_created_date]>#4/13/2009# And [State_cd]
In
("IL","AL","MI","OR","MS","KY") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") and [Discount_Flag ]=1 Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or [Ticket_created_date]>#6/4/2009# And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") and [Discount_Flag ]=1
Or
[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1 Or
[Ticket_created_date]>#6/25/2009# And [State_cd]="CO" and
[Discount_Flag ]=1
Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1
,[policy_id ]).

etc... I think you get the idea. The rest were other variations of
this
over and over. Where the criteria set for "Volume" works, any of the
criteria set I've tried for "Usage" will give me any policies where
[Discount_Flag ]=1 Or[PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1, regardless of the state critera within.

Thanks




:

Are you saying that you wish to add those additional criteria IN
PLACE OF
the others already mentioned, or IN ADDITION TO? Since you didn't
include
HOW you are trying to add those, I can only guess that you've told
Access
to
look for anything that matches the first set of criteria, OR
anything
that
matches the second set.

It almost seems like you could create a query that returns ONLY the
records
that meet those criteria, rather than try to do so via an IIF()
statement.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

My first iif statement reads as follows:

Volume: IIf([Ticket_created_date]>#4/13/2009# And [State_cd] In
("IL","AL","MI","OR","MS","KY") Or
[Ticket_created_date]>#5/7/2009# And
[State_cd] In ("TX","MO","WI") Or [Ticket_created_date]>#6/4/2009#
And
[State_cd] In ("AZ","IN","MN","OK","PA","UT") Or
[Ticket_created_date]>#6/25/2009# And
[State_cd]="CO",[policy_id ])

and works well. Now I want to build a field called "Usage" with
the
same
criteria as above and additional criteria as follows:

[Discount_Flag ]=1 Or [PreCredit_Retiering_Flag ]=1 Or
[Credit_Retiering_Flag ]=1.

I've added the additonal criteria six different ways to Sunday,
and the
result I get each time includes items in the States that I've
already
filtered out.

Ticket_Created_Date Volume Usage State_Cd
7/7/2009 2312638 FL
7/7/2009 3067181 SC
7/7/2009 3033630 3033630 IN
7/7/2009 3121385 3121385 TX
7/7/2009 3154413 3154413 CO
7/7/2009 3225028 3225028 MN
7/7/2009 3241041 3241041 MN
7/7/2009 3364325 3364325 AZ
Click to expand...
Click to expand...
Click to expand...
 
Back
Top