|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 23rd, 2009, 07:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
parsing a field with right string
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? 
|
|
#2
September 23rd, 2009, 08:19 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
parsing a field with right string
You want to use a Mid statement.
-- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "mibc" wrote: 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?
|
|
#3
September 23rd, 2009, 08:38 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
parsing a field with right string
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 mibc wrote: 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?
|
|
#4
September 23rd, 2009, 08:56 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
parsing a field with right string
On Sep 23, 3:38*pm, John Spencer wrote:
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 mibc wrote: 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 - - 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?
|
|
#5
September 23rd, 2009, 09:11 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
parsing a field with right string
On Sep 23, 3:56*pm, mibc wrote:
On Sep 23, 3:38*pm, John Spencer wrote: 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 mibc wrote: 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 - - 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?- 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))
|
|
#6
September 24th, 2009, 12:11 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
parsing a field with right string
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 mibc wrote: On Sep 23, 3:38 pm, John Spencer wrote: 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 mibc wrote: 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 - - 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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
