Error in IIf Expression
in [Queries]
|
Prev: Concatenate same field records into one - FOLLOW UP
Next: How to concatenate remarks for a given purchase request
From: Pamela on 4 Jan 2010 11:33 I have an expression in my query to create a concatenated text depending upon the value of control Measurements. If the control is skipped - meaning that there are no Measurements, I'd like to return an expression to that extent. Here's my expression: Expr1: IIf([Measurements]=Null,"The damage is not such that it has a measurement height.","The height of the damage is " & [Measurements] & " inches.") I'm not getting an error, but I'm not getting it to read the True statement. It reads even the Null value as False and returns "The height of the damage is inches. Please help me see what I'm missing here! Thanks so much! Pamela
From: Bob Barrows on 4 Jan 2010 11:41 Pamela wrote: > I have an expression in my query to create a concatenated text > depending upon the value of control Measurements. If the control is > skipped - meaning that there are no Measurements, I'd like to return > an expression to that extent. Here's my expression: > Expr1: IIf([Measurements]=Null,"The damage is not such that it has a > measurement height.","The height of the damage is " & [Measurements] > & " inches.") > I'm not getting an error, but I'm not getting it to read the True > statement. It reads even the Null value as False and returns "The > height of the damage is inches. Please help me see what I'm missing > here! Thanks so much! Pamela Nothing is ever equal to Null. Something either Is or Is Not Null. So the immediate change is : IIf([Measurements] Is Null," -- HTH, Bob Barrows
From: KARL DEWEY on 4 Jan 2010 11:52 Maybe it is not null but a zero lenght string. Use this -- Expr1: IIf([Measurements]=Null OR [Measurements]="","The damage is not such that it has a measurement height.","The height of the damage is " & [Measurements] & " inches.") -- Build a little, test a little. "Pamela" wrote: > I have an expression in my query to create a concatenated text depending upon > the value of control Measurements. If the control is skipped - meaning that > there are no Measurements, I'd like to return an expression to that extent. > Here's my expression: > Expr1: IIf([Measurements]=Null,"The damage is not such that it has a > measurement height.","The height of the damage is " & [Measurements] & " > inches.") > I'm not getting an error, but I'm not getting it to read the True statement. > It reads even the Null value as False and returns "The height of the damage > is inches. Please help me see what I'm missing here! Thanks so much! > Pamela
From: Pamela on 4 Jan 2010 11:59 Thanks, Karl, But that still didn't do it. I had actually tried that same thing earlier instead of the "Null" but I now tried it w/ the OR as you suggested and I still get the same thing: "The height of the damage is inches." Any other ideas??? "KARL DEWEY" wrote: > Maybe it is not null but a zero lenght string. > Use this -- > Expr1: IIf([Measurements]=Null OR [Measurements]="","The damage is not such > that it has a measurement height.","The height of the damage is " & > [Measurements] & " inches.") > > -- > Build a little, test a little. > > > "Pamela" wrote: > > > I have an expression in my query to create a concatenated text depending upon > > the value of control Measurements. If the control is skipped - meaning that > > there are no Measurements, I'd like to return an expression to that extent. > > Here's my expression: > > Expr1: IIf([Measurements]=Null,"The damage is not such that it has a > > measurement height.","The height of the damage is " & [Measurements] & " > > inches.") > > I'm not getting an error, but I'm not getting it to read the True statement. > > It reads even the Null value as False and returns "The height of the damage > > is inches. Please help me see what I'm missing here! Thanks so much! > > Pamela
From: Bob Barrows on 4 Jan 2010 12:27
I repeat: IIf([Measurements]=Null will always return false. Nothing is ever considered to be equal to Null. At the very least, you need to change it to: IIf([Measurements] Is Null or use the IsNull() function: Iif(IsNull([Measurements]), Pamela wrote: > Thanks, Karl, > But that still didn't do it. I had actually tried that same thing > earlier instead of the "Null" but I now tried it w/ the OR as you > suggested and I still get the same thing: "The height of the damage > is inches." > Any other ideas??? > > "KARL DEWEY" wrote: > >> Maybe it is not null but a zero lenght string. >> Use this -- >> Expr1: IIf([Measurements]=Null OR [Measurements]="","The damage is >> not such that it has a measurement height.","The height of the >> damage is " & [Measurements] & " inches.") >> >> -- >> Build a little, test a little. >> >> >> "Pamela" wrote: >> >>> I have an expression in my query to create a concatenated text >>> depending upon the value of control Measurements. If the control >>> is skipped - meaning that there are no Measurements, I'd like to >>> return an expression to that extent. Here's my expression: >>> Expr1: IIf([Measurements]=Null,"The damage is not such that it has a >>> measurement height.","The height of the damage is " & >>> [Measurements] & " inches.") >>> I'm not getting an error, but I'm not getting it to read the True >>> statement. It reads even the Null value as False and returns "The >>> height of the damage is inches. Please help me see what I'm >>> missing here! Thanks so much! Pamela -- HTH, Bob Barrows |