Nested IIF problems

[Brian Collins]

I am trying to assign a transaction type to a field.
There are six types - number 1 to 6
I have a nested IIF statement as follows



Automatrix:
IIf([T01_globalFinancial]![CMS REF]="0Z1037.00472",1,

IIf([T01_globalFinancial]![CMS REF]="0z1037.00672" And [T01_globalFinancial]![Costs to Date (VAT INCL)]>=0,2,

IIf([T01_globalFinancial]![CMS REF]="0z1037.00673" And [T01_globalFinancial]![Costs to Date (VAT INCL)]>=0,3,

IIf([T01_globalFinancial]![CMS REF]="0z1037.00672" And [T01_globalFinancial]![Costs to Date (VAT INCL)]="£0.00",4,

IIf([T01_globalFinancial]![CMS REF]="0z1037.00673" And [T01_globalFinancial]![Costs to Date (VAT INCL)]="£0.00",5,6)))))

Condition 1 2 3 and 6 work fine.

Currently there are no records meeting condition 4 but I am assuming it have the same problem as condition 5

Condition 5 gives me condition 3.

The Costs to Date field is set to currency and field contents are set to £0.00.

I have tried using 0, null, <=1 but the result is the same.

The IIF statements are split out for ease of reading.

Any ideas please?

[weekend00]

Obviously, records matching condition 5 must match condition 3, since condition 3 is in prior to condition 5, you get only 3.

And, when I compared a currency field with a string ( e.g. [T01_globalFinancial]![Costs to Date (VAT INCL)]="£0.00",) in my access 2003, I got type mismatch error.

[Brian Collins]

Doh!!! what a numpty

Problem lies in the >=0

Which, as you say, is the same condition for 4 and 5.

Problem solved.

Tks chum