Nested If Statements Using Or (Logic Error)

[IFA Stamford]

All,

Can someone please look at my code and see what might be the error in my logic?

Product Level 1: IIf([Monthly Client Detail 2010]![Fact Type Desc]="Writedown L4" Or [Monthly Client Detail 2010]![Fact Type Desc]="Total Exceptions Items L4","Not Defined",IIf([Monthly Client Detail 2010]![Global Banking Product L3]="Cross Sell","Cross Sell",IIf(Left([Monthly Client Detail 2010]![Mid Level],3) In ("DCM","Syn"),"DCM",IIf(Right([Monthly Client Detail 2010]![MT Pack Lo Level 4],3)="DCM","DCM",IIf([Monthly Client Detail 2010]![Mid Level]="Other","Markets",IIf([Monthly Client Detail 2010]![Mid Level]="Equities","Equities Origination",[Monthly Client Detail 2010]![Mid Level]))))))

The problem is in the grouping in red. When the Make Table creates the query, it isn't taking into account the records that have those fields. It is categorizing them as any one of the other items based on the Mid Level Column.



Can you help me understand what I am doing incorrectly?

Thanks,

[ajetrumpet]

I would guess the problem is in your statements with the bang (!) operator. Because usually there are 3 entities or references that accompany it.

So...what is ''Monthly Client Detail''?? a table? form? and what is ''Mid Level''???

[IFA Stamford]

All,

Can someone please look at my code and see what might be the error in my logic?

Product Level 1: IIf([Monthly Client Detail 2010]![Fact Type Desc]="Writedown L4" Or [Monthly Client Detail 2010]![Fact Type Desc]="Total Exceptions Items L4","Not Defined",IIf([Monthly Client Detail 2010]![Global Banking Product L3]="Cross Sell","Cross Sell",IIf(Left([Monthly Client Detail 2010]![Mid Level],3) In ("DCM","Syn"),"DCM",IIf(Right([Monthly Client Detail 2010]![MT Pack Lo Level 4],3)="DCM","DCM",IIf([Monthly Client Detail 2010]![Mid Level]="Other","Markets",IIf([Monthly Client Detail 2010]![Mid Level]="Equities","Equities Origination",[Monthly Client Detail 2010]![Mid Level]))))))

The problem is in the grouping in red. When the Make Table creates the query, it isn't taking into account the records that have those fields. It is categorizing them as any one of the other items based on the Mid Level Column.

Can you help me understand what I am doing incorrectly?

Thanks,

I would guess the problem is in your statements with the bang (!) operator. Because usually there are 3 entities or references that accompany it.

So...what is ''Monthly Client Detail''?? a table? form? and what is ''Mid Level''???

Adam,

Monthly Client Detail is the Table. Mid Level is one of the columns of data that resides in the table.

[ajetrumpet]

Adam,

Monthly Client Detail is the Table. Mid Level is one of the columns of data that resides in the table.

well I see a problem right off, right there. How in the world can Access know which record you are referring to?

If you're referring to the actual field of the record that's being evaluated AT THAT TIME specifically, the field surrounded by brackets is all you need. Nothing else.

I don't know if the current way works, but I've never seen it used. And if you compare it to domain aggregate functions such as DSUM() or DCOUNT(), doing that sort of referencing will return the first record's value everytime.

But that aside, please change those refs to the actual field names only first, then if it doesn't work, post back. One thing if any, it will make the statement much more readable. Thanks!

[pbaldy]

Are you sure that the field contains those text values and not a numeric ID field, as might be the case with a lookup field? Can you post a sample of the db?

[IFA Stamford]

well I see a problem right off, right there. How in the world can Access know which record you are referring to?

If you're referring to the actual field of the record that's being evaluated AT THAT TIME specifically, the field surrounded by brackets is all you need. Nothing else.

I don't know if the current way works, but I've never seen it used. And if you compare it to domain aggregate functions such as DSUM() or DCOUNT(), doing that sort of referencing will return the first record's value everytime.

But that aside, please change those refs to the actual field names only first, then if it doesn't work, post back. One thing if any, it will make the statement much more readable. Thanks!

The information spits out eaxctly 64,057 records, which is exactly the number of records I started with. The only place where there is an issue, is where those fields show Writedown L4 and Total Exception Items L4. This is the problem. The 97 records that have either of those two inputs should return TRUE on the logic test and therefore should come back Not Defined. Instead it continues down the logic path to the last parameter, and comes back with whatever information resides in the Mid Level column.

[weekend00]

The information spits out eaxctly 64,057 records, which is exactly the number of records I started with. The only place where there is an issue, is where those fields show Writedown L4 and Total Exception Items L4. This is the problem. The 97 records that have either of those two inputs should return TRUE on the logic test and therefore should come back Not Defined. Instead it continues down the logic path to the last parameter, and comes back with whatever information resides in the Mid Level column.

That means your expression is almost working, at least the syntax is correct.
usually when we see your first post, we think about syntax, you misled us!

maybe you should take a careful look at the data with "Writedown L4" or "Total Exception Items L4", check the spaces in front or in between words.

[IFA Stamford]

That means your expression is almost working, at least the syntax is correct.
usually when we see your first post, we think about syntax, you misled us!

maybe you should take a careful look at the data with "Writedown L4" or "Total Exception Items L4", check the spaces in front or in between words.

Ok. Found my problem. I was using the wrong field to do the logic on Writedown L4 and Total Exception Items L4. Also, it wasn't Total Exception Items L4 but Total Exceptional Items L4.

Sorry. Have to scrutinize my work more often.