Expresson Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michelle - 28 Sep 2006 18:42 GMT

I have the following expression that takes the last name and inmate number
and combine them. It works however if the field is blank I get ,0 int he
field. I just want it to be blank.

=IIf(InStr([IN_NAME],",")>1,Left([IN_NAME],InStr([IN_NAME],",")-1),False) &
", " & [IN_INMNUM]

Reply to this Message

Barry Gilbert - 28 Sep 2006 19:31 GMT

> I have the following expression that takes the last name and inmate number
> and combine them. It works however if the field is blank I get ,0 int he
> field. I just want it to be blank.
>
> =IIf(InStr([IN_NAME],",")>1,Left([IN_NAME],InStr([IN_NAME],",")-1),False) &
> ", " & [IN_INMNUM]

I see three issues:
If the first character is a comma, it will pass the iif and return the comma.
If it's not, it will return False, which equals 0.
You're appending the comma delimiter whether or not there is a name. Moving
insode the IIF will append it only if there is a name.

I think changing it to the following will fix it.

=IIf(InStr([IN_NAME],",")>0,Left([IN_NAME],InStr([IN_NAME],",")-1) & ",") &
[IN_INMNUM]

Barry

Reply to this Message

Tim Ferguson - 28 Sep 2006 21:08 GMT

> I have the following expression that takes the last name and inmate
> number and combine them. It works however if the field is blank I get
> ,0 int he field. I just want it to be blank.
>
> =IIf(InStr([IN_NAME],",")>1,Left([IN_NAME],InStr([IN_NAME],",")-1),Fals
> e) & ", " & [IN_INMNUM]

=IIf( _
InStr([IN_NAME],",")>1, _
Left([IN_NAME],InStr([IN_NAME],",")-1), _
Null) + _
", " & [IN_INMNUM]

If the comparison returns false (i.e. no comma or comma in the first
position) then the IIf() returns Null which will propagate throughout the
entire expression.

An alternative:

=IIf( _
InStr([IN_NAME],",")>1, _
Left([IN_NAME],InStr([IN_NAME],",")-1) & ", " & [IN_INMNUM], _
"")

which has the advantages of being easier to read and always returning a
string.

Hope that helps

Tim F

Reply to this Message