Mid string null value
in [Queries]
|
Prev: in access, round up to nearest fraction(.5)
Next: Obtaining the correct results from a two table query
From: Jim on 19 May 2010 13:52 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
From: Bob Barrows on 19 May 2010 14:24 Jim wrote: > 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 You will need to use Iif: Iif(InStr([Description],"#") > 0,Mid([Description],InStr([Description],"#")+1)) -- HTH, Bob Barrows
From: Daryl S on 19 May 2010 15:05 Jim - Test for the # first, like this: IIf(InStr(1, "Description", "#", 1) = 0, "", Mid("Description", InStr("Description", "#") + 1, Len("Description") - InStr("Description", "#"))) -- Daryl S "Jim" wrote: > 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
From: Jim on 19 May 2010 15:15 Thank you both for the quick responses - Bob's code worked great! "Bob Barrows" wrote: > Jim wrote: > > 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 > > You will need to use Iif: > > Iif(InStr([Description],"#") > > 0,Mid([Description],InStr([Description],"#")+1)) > > -- > HTH, > Bob Barrows > > > . >
From: John Spencer on 19 May 2010 16:12
Try this variation Mid([Description],InStr([Description] & "#","#")+1) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Jim wrote: > 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 |