Extract Portions from String

J

Jason K

Hi,

I have a field that can contain two differnt variations.
Let's say the field (infoview) contains
WN1401S.C05344.MV01000.P00208 then the below logic works
fine to get 1401 out of the first node.

strFromLeftOfC = Left([infoview],InStr(1,[infoview],".")-1)
strAC = Left([infoview],2),
strNumber = Mid([infoview],3,4);

My question is, is there another query I can write to get
9222 & 1409 from below? Or can it be combined with the
above somehow?

Basically, if the letter after the first period starts
with anything but C then I need characters 2-5 if not I
need 3-6

T9222TT.P182
P1409WM.OUTL70.W1209

Thanks.

Jason
 
T

Tom Ellison

Dear Jason:

The rule you gave:

if the letter after the first period starts with anything but C then I need
characters 2-5 if not I need 3-6

could be written as:

IIf(MID(infoview, 2, 1) <> "C", MID(infoview, 2, 4), MID(infoview, 3, 4))
 
J

Jason K

Hi Tom,

Thanks for replying. The below answer works fine for the
rule where after the first period doesn't start with C.
However, the entries that do start with C after the period
are retrieving the same fields. Example
B28080.C00000.MV01000.P00204 is returning 2808 and not
8080.

Working on revising it but could use some help.

Thanks.

Jason
-----Original Message-----
Dear Jason:

The rule you gave:

if the letter after the first period starts with anything but C then I need
characters 2-5 if not I need 3-6

could be written as:

IIf(MID(infoview, 2, 1) <> "C", MID(infoview, 2, 4), MID (infoview, 3, 4))
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Jason K said:
Hi,

I have a field that can contain two differnt variations.
Let's say the field (infoview) contains
WN1401S.C05344.MV01000.P00208 then the below logic works
fine to get 1401 out of the first node.

strFromLeftOfC = Left([infoview],InStr(1, [infoview],".")-1)
strAC = Left([infoview],2),
strNumber = Mid([infoview],3,4);

My question is, is there another query I can write to get
9222 & 1409 from below? Or can it be combined with the
above somehow?

Basically, if the letter after the first period starts
with anything but C then I need characters 2-5 if not I
need 3-6

T9222TT.P182
P1409WM.OUTL70.W1209

Thanks.

Jason
Click to expand...


.
Click to expand...
 
T

Tom Ellison

Dear Jason:

Well, your rule said, "if the letter after the first period starts with
anything but C then I need characters 2-5 if not I need 3-6."

I accidentally omitted "after the first period" from reading this rule.
Sorry. So it should be:

IIf(MID(infoview, INSTR(infoview, ".") + 1, 1) <> "C", MID(infoview, 2, 4),
MID(infoview, 3, 4))

Hope this helps.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Jason K said:
Hi Tom,

Thanks for replying. The below answer works fine for the
rule where after the first period doesn't start with C.
However, the entries that do start with C after the period
are retrieving the same fields. Example
B28080.C00000.MV01000.P00204 is returning 2808 and not
8080.

Working on revising it but could use some help.

Thanks.

Jason
-----Original Message-----
Dear Jason:

The rule you gave:

if the letter after the first period starts with anything but C then I need
characters 2-5 if not I need 3-6

could be written as:

IIf(MID(infoview, 2, 1) <> "C", MID(infoview, 2, 4), MID (infoview, 3, 4))
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Jason K said:
Hi,

I have a field that can contain two differnt variations.
Let's say the field (infoview) contains
WN1401S.C05344.MV01000.P00208 then the below logic works
fine to get 1401 out of the first node.

strFromLeftOfC = Left([infoview],InStr(1, [infoview],".")-1)
strAC = Left([infoview],2),
strNumber = Mid([infoview],3,4);

My question is, is there another query I can write to get
9222 & 1409 from below? Or can it be combined with the
above somehow?

Basically, if the letter after the first period starts
with anything but C then I need characters 2-5 if not I
need 3-6

T9222TT.P182
P1409WM.OUTL70.W1209

Thanks.

Jason
Click to expand...


.
Click to expand...
Click to expand...
 
J

Jason K

Tom,

Sorry if I wasn't clear on my post. Lesson learned. Your
new function worked like a charm.

Thanks a lot!

MVP's Rule!

Have a great day.

Jason
-----Original Message-----
Dear Jason:

Well, your rule said, "if the letter after the first period starts with
anything but C then I need characters 2-5 if not I need 3- 6."

I accidentally omitted "after the first period" from reading this rule.
Sorry. So it should be:

IIf(MID(infoview, INSTR(infoview, ".") + 1, 1) <> "C", MID (infoview, 2, 4),
MID(infoview, 3, 4))

Hope this helps.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Jason K said:
Hi Tom,

Thanks for replying. The below answer works fine for the
rule where after the first period doesn't start with C.
However, the entries that do start with C after the period
are retrieving the same fields. Example
B28080.C00000.MV01000.P00204 is returning 2808 and not
8080.

Working on revising it but could use some help.

Thanks.

Jason
-----Original Message-----
Dear Jason:

The rule you gave:

if the letter after the first period starts with
Click to expand...
anything
but C then I need
characters 2-5 if not I need 3-6

could be written as:

IIf(MID(infoview, 2, 1) <> "C", MID(infoview, 2, 4),
Click to expand...
MID
(infoview, 3, 4))
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Hi,

I have a field that can contain two differnt variations.
Let's say the field (infoview) contains
WN1401S.C05344.MV01000.P00208 then the below logic works
fine to get 1401 out of the first node.

strFromLeftOfC = Left([infoview],InStr(1, [infoview],".")-1)
strAC = Left([infoview],2),
strNumber = Mid([infoview],3,4);

My question is, is there another query I can write to get
9222 & 1409 from below? Or can it be combined with the
above somehow?

Basically, if the letter after the first period starts
with anything but C then I need characters 2-5 if not I
need 3-6

T9222TT.P182
P1409WM.OUTL70.W1209

Thanks.

Jason


.
Click to expand...
Click to expand...


.
Click to expand...
 
T

Tom Ellison

Dear Jason:

No, your post was perfectly clear. I have now cleaned my glasses and may
have less problem in the future.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Jason K said:
Tom,

Sorry if I wasn't clear on my post. Lesson learned. Your
new function worked like a charm.

Thanks a lot!

MVP's Rule!

Have a great day.

Jason
-----Original Message-----
Dear Jason:

Well, your rule said, "if the letter after the first period starts with
anything but C then I need characters 2-5 if not I need 3- 6."

I accidentally omitted "after the first period" from reading this rule.
Sorry. So it should be:

IIf(MID(infoview, INSTR(infoview, ".") + 1, 1) <> "C", MID (infoview, 2, 4),
MID(infoview, 3, 4))

Hope this helps.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Jason K said:
Hi Tom,

Thanks for replying. The below answer works fine for the
rule where after the first period doesn't start with C.
However, the entries that do start with C after the period
are retrieving the same fields. Example
B28080.C00000.MV01000.P00204 is returning 2808 and not
8080.

Working on revising it but could use some help.

Thanks.

Jason

-----Original Message-----
Dear Jason:

The rule you gave:

if the letter after the first period starts with anything
but C then I need
characters 2-5 if not I need 3-6

could be written as:

IIf(MID(infoview, 2, 1) <> "C", MID(infoview, 2, 4), MID
(infoview, 3, 4))
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Hi,

I have a field that can contain two differnt variations.
Let's say the field (infoview) contains
WN1401S.C05344.MV01000.P00208 then the below logic works
fine to get 1401 out of the first node.

strFromLeftOfC = Left([infoview],InStr(1,
[infoview],".")-1)
strAC = Left([infoview],2),
strNumber = Mid([infoview],3,4);

My question is, is there another query I can write to
get
9222 & 1409 from below? Or can it be combined with the
above somehow?

Basically, if the letter after the first period starts
with anything but C then I need characters 2-5 if not I
need 3-6

T9222TT.P182
P1409WM.OUTL70.W1209

Thanks.

Jason


.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extracting text from a declared string 2

Top