right string problem

[mbc04]

I have a field, OCLC NO, that contains info in various formats and
various lengths. I want to extract only the part that comes after ocm.

These are the 3 types of information contained in this field:
(DLC)97048845
(OCoLC)ocm36214720
ocm23769753

My query contains this formula: OCLC: Right([OCLC NO],InStr([OCLC
NO],"ocm"))

These are the results I get:

blank (this is what I want because ocm is not contained anywhere in
the first example)
36214720 (this is correctly giving me everything after ocm)
3 (this is only giving me the last digit; why?)


My results are consistent; every one looks fine except those fields
that contain just ocm and a number.

 

[Guest]

InStr([OCLCNO],"ocm") returns the position of 'ocm' so in the last one 'ocm'
is in position one and therefore only one character is returned from the
right.
Try using InStrRev([OCLCNO],"ocm") to count position from the right.

In the first there is no 'ocm' so the position is zero and returns zero
characters or blank. Use an IIF statement for when there is no 'ocm.'

 

[Guest]

OCLC: IIF(InStr([OCLC NO],"ocm") = 0, "", Mid$([OCLC NO],InStr([OCLC
NO],"ocm")+4

Try to use the $ on the function name when you can.

 

[mbc04]

OCLC: IIF(InStr([OCLC NO],"ocm") = 0, "", Mid$([OCLC NO],InStr([OCLC
NO],"ocm")+4

Try to use the $ on the function name when you can.

--
Steve Clark,
Former Access MVP
FMS, Inchttp://www.fmsinc.com/consulting

I have a field, OCLC NO, that contains info in various formats and
various lengths. I want to extract only the part that comes after ocm.

These are the 3 types of information contained in this field:
(DLC)97048845
(OCoLC)ocm36214720
ocm23769753

My query contains this formula: OCLC: Right([OCLC NO],InStr([OCLC
NO],"ocm"))

These are the results I get:

blank (this is what I want because ocm is not contained anywhere in
the first example)
36214720 (this is correctly giving me everything after ocm)
3 (this is only giving me the last digit; why?)

My results are consistent; every one looks fine except those fields
that contain just ocm and a number.- Hide quoted text -

- Show quoted text -

This is better but on the 3rd example, I'm still missing the first
digit -- I get 3769753 instead of 23769753.

 

[mbc04]

OCLC: IIF(InStr([OCLC NO],"ocm") = 0, "", Mid$([OCLC NO],InStr([OCLC
NO],"ocm")+4

Try to use the $ on the function name when you can.

I have a field, OCLC NO, that contains info in various formats and
various lengths. I want to extract only the part that comes after ocm.
These are the 3 types of information contained in this field:
(DLC)97048845
(OCoLC)ocm36214720
ocm23769753
My query contains this formula: OCLC: Right([OCLC NO],InStr([OCLC
NO],"ocm"))
These are the results I get:
blank (this is what I want because ocm is not contained anywhere in
the first example)
36214720 (this is correctly giving me everything after ocm)
3 (this is only giving me the last digit; why?)
My results are consistent; every one looks fine except those fields
that contain just ocm and a number.- Hide quoted text -

- Show quoted text -

This is better but on the 3rd example, I'm still missing the first
digit -- I get 3769753 instead of 23769753.- Hide quoted text -

- Show quoted text -

OK, I figured it out. I made it +3 instead of +4 and it's OK now.
Thank you so very much!!!

 

[mbc04]

OCLC: IIF(InStr([OCLC NO],"ocm") = 0, "", Mid$([OCLC NO],InStr([OCLC
NO],"ocm")+4
Try to use the $ on the function name when you can.
--
Steve Clark,
Former Access MVP
FMS, Inchttp://www.fmsinc.com/consulting
:
I have a field, OCLC NO, that contains info in various formats and
various lengths. I want to extract only the part that comes after ocm.
These are the 3 types of information contained in this field:
(DLC)97048845
(OCoLC)ocm36214720
ocm23769753
My query contains this formula: OCLC: Right([OCLC NO],InStr([OCLC
NO],"ocm"))
These are the results I get:
blank (this is what I want because ocm is not contained anywhere in
the first example)
36214720 (this is correctly giving me everything after ocm)
3 (this is only giving me the last digit; why?)
My results are consistent; every one looks fine except those fields
that contain just ocm and a number.- Hide quoted text -
- Show quoted text -

This is better but on the 3rd example, I'm still missing the first
digit -- I get 3769753 instead of 23769753.- Hide quoted text -

- Show quoted text -

OK, I figured it out. I made it +3 instead of +4 and it's OK now.
Thank you so very much!!!- Hide quoted text -

- Show quoted text -

OK, I now have a new wrinkle on this. These were two variations of
data contained in my OCLC NO field
(OCoLC)ocm36214720
ocm23769753

BUT I have realized that the prefix in some of these may be ocn
instead of ocm. I tried adjusting my query by using a wildcard to
stand for either n or m like this:

OCLC: IIf(InStr([OCLC NO],"oc?")=0,"",Mid$([OCLC NO],InStr([OCLC
NO],"oc?")+3))

I tried ?, *, %, and # -- none of which worked. My results with these
were just a blank field. This is a memo field. Can I not use wildcards
in memo fields? Or is there some other way to do this?

 

[John Spencer]

INSTR does not support wild cards. If you use a wild card character it is
treated just as the character itself.

IF you are looking for either OCM or OCN, you could try the following. It
might work


MID([OCLC NO], Instr(1,[OCLC NO],IIF([OCLC NO] Like "*OCM*","OCM",IIF([OCLC
No] Like "*OCN*","OCN",[OCLC No] & "X"))))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

On Sep 19, 11:08 am, S.Clark <[email protected]> wrote:

OCLC: IIF(InStr([OCLC NO],"ocm") = 0, "", Mid$([OCLC NO],InStr([OCLC
NO],"ocm")+4

Try to use the $ on the function name when you can.

:
I have a field, OCLC NO, that contains info in various formats and
various lengths. I want to extract only the part that comes after
ocm.

These are the 3 types of information contained in this field:
(DLC)97048845
(OCoLC)ocm36214720
ocm23769753

My query contains this formula: OCLC: Right([OCLC NO],InStr([OCLC
NO],"ocm"))

These are the results I get:

blank (this is what I want because ocm is not contained anywhere
in
the first example)
36214720 (this is correctly giving me everything after ocm)
3 (this is only giving me the last digit; why?)

My results are consistent; every one looks fine except those fields
that contain just ocm and a number.- Hide quoted text -

- Show quoted text -

This is better but on the 3rd example, I'm still missing the first
digit -- I get 3769753 instead of 23769753.- Hide quoted text -

- Show quoted text -

OK, I figured it out. I made it +3 instead of +4 and it's OK now.
Thank you so very much!!!- Hide quoted text -

- Show quoted text -

OK, I now have a new wrinkle on this. These were two variations of
data contained in my OCLC NO field
(OCoLC)ocm36214720
ocm23769753

BUT I have realized that the prefix in some of these may be ocn
instead of ocm. I tried adjusting my query by using a wildcard to
stand for either n or m like this:

OCLC: IIf(InStr([OCLC NO],"oc?")=0,"",Mid$([OCLC NO],InStr([OCLC
NO],"oc?")+3))

I tried ?, *, %, and # -- none of which worked. My results with these
were just a blank field. This is a memo field. Can I not use wildcards
in memo fields? Or is there some other way to do this?

 

[mbc04]

INSTR does not support wild cards. If you use a wild card character it is
treated just as the character itself.

IF you are looking for either OCM or OCN, you could try the following. It
might work

MID([OCLC NO], Instr(1,[OCLC NO],IIF([OCLC NO] Like "*OCM*","OCM",IIF([OCLC
No] Like "*OCN*","OCN",[OCLC No] & "X"))))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

On Sep 19, 11:33 am, (e-mail address removed) wrote:
OCLC: IIF(InStr([OCLC NO],"ocm") = 0, "", Mid$([OCLC NO],InStr([OCLC
NO],"ocm")+4
Try to use the $ on the function name when you can.
--
Steve Clark,
Former Access MVP
FMS, Inchttp://www.fmsinc.com/consulting
:
I have a field, OCLC NO, that contains info in various formats and
various lengths. I want to extract only the part that comes after
ocm.
These are the 3 types of information contained in this field:
(DLC)97048845
(OCoLC)ocm36214720
ocm23769753
My query contains this formula: OCLC: Right([OCLC NO],InStr([OCLC
NO],"ocm"))
These are the results I get:
blank (this is what I want because ocm is not contained anywhere
in
the first example)
36214720 (this is correctly giving me everything after ocm)
3 (this is only giving me the last digit; why?)
My results are consistent; every one looks fine except those fields
that contain just ocm and a number.- Hide quoted text -
- Show quoted text -
This is better but on the 3rd example, I'm still missing the first
digit -- I get 3769753 instead of 23769753.- Hide quoted text -
- Show quoted text -
OK, I figured it out. I made it +3 instead of +4 and it's OK now.
Thank you so very much!!!- Hide quoted text -
- Show quoted text -

OK, I now have a new wrinkle on this. These were two variations of
data contained in my OCLC NO field
(OCoLC)ocm36214720
ocm23769753

BUT I have realized that the prefix in some of these may be ocn
instead of ocm. I tried adjusting my query by using a wildcard to
stand for either n or m like this:

OCLC: IIf(InStr([OCLC NO],"oc?")=0,"",Mid$([OCLC NO],InStr([OCLC
NO],"oc?")+3))

I tried ?, *, %, and # -- none of which worked. My results with these
were just a blank field. This is a memo field. Can I not use wildcards
in memo fields? Or is there some other way to do this?- Hide quoted text -

- Show quoted text -

I tried this and got results like these:
ocm11345678
ocn2436587

What I want is just the number, without any prefixes. How can I do
that?