Mid string null value

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I'm using the code below to display everthing after the pound sign (#) and it
works for the most part, but displays the whole field for fields that don't
have a pound sign.

My goal is to display everything after the # if there is a #, otherwise
leave the field blank.

Mid([Description],InStr([Description],"#")+1)

Thanks,
Jim
 
Jim said:
I'm using the code below to display everthing after the pound sign
(#) and it works for the most part, but displays the whole field for
fields that don't have a pound sign.

My goal is to display everything after the # if there is a #,
otherwise leave the field blank.

Mid([Description],InStr([Description],"#")+1)

Thanks,
Jim
Click to expand...

You will need to use Iif:

Iif(InStr([Description],"#") >
0,Mid([Description],InStr([Description],"#")+1))
 
Jim -

Test for the # first, like this:

IIf(InStr(1, "Description", "#", 1) = 0, "", Mid("Description",
InStr("Description", "#") + 1, Len("Description") - InStr("Description",
"#")))
 
Thank you both for the quick responses - Bob's code worked great!

Bob Barrows said:
Jim said:
I'm using the code below to display everthing after the pound sign
(#) and it works for the most part, but displays the whole field for
fields that don't have a pound sign.

My goal is to display everything after the # if there is a #,
otherwise leave the field blank.

Mid([Description],InStr([Description],"#")+1)

Thanks,
Jim
Click to expand...

You will need to use Iif:

Iif(InStr([Description],"#") >
0,Mid([Description],InStr([Description],"#")+1))

--
HTH,
Bob Barrows


.
Click to expand...
 
Try this variation

Mid([Description],InStr([Description] & "#","#")+1)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top