ERROR msg WHILE USING IIF FORMULA

[CUCKOO]

In IIF formula if I use Left formula as false part I get a msg "THE EXPRESSION COULD NOT BE SAVED BECAUSE ITS RESULT TYPE, SUCH AS BINARY OR NULL, IS NOT SUPPORTED BY SERVER".


See the example below

=IIF(EXPRESSION,TRUE PART,LEFT(XYZ,1))

[ssanfu]

Sounds like the wrong value type is trying to be stored in the field.
What is the field type that the value is going to be saved in? Can the field type handle a binary or NULL value?

[CUCKOO]

Sounds like the wrong value type is trying to be stored in the field.
What is the field type that the value is going to be saved in? Can the field type handle a binary or NULL value?

Thanks for reply

Field type is text

cuckoo

[rpeare]

How about typing out exactly what your iif statement is, what are you testing your string for? length? character content?

[CUCKOO]

how about typing out exactly what your iif statement is, what are you testing your string for? Length? Character content?

my table with first row header

p1 p2 p3 p4 p5 chc 26 da ep cfc 36 da ip rgtho r 56 dalh h3 mgo r 76 da op ygc 39 da jp

expression

iif(left([p1],1)="c",left([p3],1)&left([p1],2),left([p3],1)&left([p1],1))

field type ----- text

[rpeare]

dup post deleted

[rpeare]

I ran a test on the exact data you gave and I get the results

2CH
3CF
5R
7M
3Y

Is this iif statement in a query or is it a formula on a form or report? are you sure this is what's causing your error message?

EDIT: one more thing, are you you absolutely certain that your p1 and p3 fields *always* have a value? if they can be or are null values in your table that could be causing some problems as well

[CUCKOO]

Thanks

I am using the formula in Table calculated field.

P1 is never Null but P3 can be. Is this the reason for getting error.

I ran a test on the exact data you gave and I get the results

2CH
3CF
5R
7M
3Y

Is this iif statement in a query or is it a formula on a form or report? are you sure this is what's causing your error message?

EDIT: one more thing, are you you absolutely certain that your p1 and p3 fields *always* have a value? if they can be or are null values in your table that could be causing some problems as well

[rpeare]

In all likelyhood yes, try this:

iif(left([p1],1)="c",(iif(isnull([p3], null,left([p3],1)))&left([p1],2),(iif(isnull([p3], null,left([p3],1)))&left([p1],1))

I didn't test it but you get the idea.

[ssanfu]

Or use the NZ() function (Null to zero) to change the Null to a number or empty string. See help.