parsing a field with right string

parsing a field with right string

Post by mibc » Fri, 25 Sep 2009 03:13:27


OCLC: Right([Bibs without items]![OCLC NO],InStr([Bibs without items]!
[OCLC NO],"ocm"))

I want only the numbers that come after ocm in the OCLC NO field to be
returned in a new field called OCLC. The number is not always the same
length. It seems to work perfectly on the last two examples but only
returns the last digit on the first two examples.

BIB_ID Title OCLC NO
ISBN OCLC
207002 Whales / ocm18223778 9780690047
8
72171 Dog / ocm23253000 9780679814 0
276478 Cougars / (OCoLC)ocm48620192
9780736813 48620192
253703 Dogs and wild Dogs / (OCoLC)ocm52305894 9780792282 52305894

I tried changing the last part to InStr([Bibs without items]![OCLC
NO],"m")) and that gave me two more digits to the left (778, 000,
cm48620192, cm52305894). What am I doing wrong?
 
 
 

parsing a field with right string

Post by SmVycnkgV2 » Fri, 25 Sep 2009 04:19:01

You want to use a Mid statement.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

 
 
 

parsing a field with right string

Post by John Spenc » Fri, 25 Sep 2009 04:38:14

That sample is hard to read. Perhaps you could post just the values of OCLC
NO that you are trying to parse. I think you might be able to use the following.

MID([Bibs without items]![OCLC NO],
InStr([Bibs without items]![OCLC NO],"ocm")+3)


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

parsing a field with right string

Post by mibc » Fri, 25 Sep 2009 04:56:50


> > 207002 Whales / cm18223778 gt;gt;> > 780690047
> > 8
> > 72171 og / ocm2325>0>0 780679814 gt;gt;0
> > 276478 Cougars>/>(OCoLC)ocm48620192
> > 9780736813 48620192
> > 25370> gt;gt;Dogs and wild Dogs / OCoLC)ocm52305894 780792282 52305894
>> > > I tried changing the last part to nStr([Bibs without items]![OC>C> > > NO],"m")) and that gave me two more digits to the left (778, 000,> >>> cm48620192, cm52305894). What am I doing wrong?- Hide quoted text -
>
> - Show quoted text -

MID does work just fine for these examples. Thanks! Unfortunately I
also have some records that are like this:
(DLC)2004002220 LC)2004002220

The OCLC field does not contain the letters ocm anywhere, so I would
like the resulting field to be empty but I'm getting LC)2004002220.
Any suggestions?
 
 
 

parsing a field with right string

Post by mibc » Fri, 25 Sep 2009 05:11:08


> > > 207002 Whales / cm18223778 gt;gt;gt;> > > 780690047
> > > 8
> > > 72171 og / ocm2325>0>0>780679814 gt;gt;gt;0
> > > 276478 Cougars />gt;gt;(OCoLC)ocm48620192
> > > 9780736813 48620192
> > > 25370> gt;gt;D>gs and wild Dogs / OCoLC)ocm52305894 780792282 52305894
>
> > > I tried changing the last part to nStr([Bibs without items]![OCLC> > > > NO],"m")) and that gave me two more digits to the left (778, 000,
>
> > - Show quoted text - >>
> MID does work just fine for these exampl>s. Thanks! Unfortunately I
> also h>ve>some records that are like this:
> (DLC)2004002220 LC)20040022>0
>
> The OCLC field does not contain the letters ocm anywhere, so I>would
> like the resulting field to be>em>ty but I'm getting LC)2004002220.
> Any suggestions?- Hide quoted text -
>
> - Show quoted text -

Hey, I got it --- this works. Thanks for setting me on the right track
--- it was most helpful.
OCLC: IIf(InStr([Bibs without items]![OCLC NO],"ocm")=0,"",Mid([Bibs
without items]![OCLC NO],InStr([Bibs without items]![OCLC NO],"ocm")
+3))
 
 
 

parsing a field with right string

Post by John Spenc » Fri, 25 Sep 2009 08:11:44

Here is a little trick that will work.

MID([Bibs without items]![OCLC NO]
, InStr([Bibs without items]![OCLC NO] & "ocm","ocm")+3)

Now if there is no "OCM" in the field the instr will return the length of the
string + 3. So Mid will return everything from the string after the length of
the string plus 3 - effectively a zero length string.


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