Expresson Problem

[Guest]

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]

 

[Guest]

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

 

[Tim Ferguson]

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