parsing a field with right string

M

mibc

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?
 
J

John Spencer

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
 
M

mibc

That sample is hard to read.  Perhaps you could post just the values ofOCLC
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


OCLC: Right([Bibs without items]![OCLC NO],InStr([Bibs without items]!
[OCLC NO],"ocm"))
Click to expand...
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.
Click to expand...
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
Click to expand...
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?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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?
 
M

mibc

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.
Click to expand...
MID([Bibs without items]![OCLC NO],
InStr([Bibs without items]![OCLC NO],"ocm")+3)
Click to expand...
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
mibc said:
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?- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

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?- Hide quoted text -

- Show quoted text -
Click to expand...

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))
 
J

John Spencer

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
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


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?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

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?
Click to expand...
 
Top