I need part of a memo field

[2Blessed4Stress]

I have a memo fleld called "TITLE". I need to extract part of the field that
contains this information: TM:T-6225-CT-MMC-010/59180 (but only the part
after the ":")
It may appear at the very end (nothing would be after /59180)
It may appear in the middle of other words or it may not appear at all.

I tried using:
NSTM: Mid([TITLE],InStr([TITLE],":")+1,InStr([TITLE],", ")+1)
It only worked in some records and not others.

For example. When the TITLE was:
FUEL OIL SERVICE PUMPS CTJ12LM-250, INSTRUCTION MANUAL; MFR
TM:T-6225-CS-MMC-010/59180
My results were right: T-6225-CS-MMC-010/59180

But when the TITLE was:
PRIMARY & SECONDARY SAMPLE COOLERS, TECHNICAL MANUAL FOR HEAT TRANSFER
PRODUCTS; MFR TM:T-9511-AK-MMC-010/51657
My results wereWRONG: T-9511-AK-MMC-010/

What should I be doing in my query to get the right results?

 

[John Spencer]

Try building this a bit at a time.

Get the Value and anything after it
Mid([TITLE],InStr([TITLE],":")+1)

Strip of trailing portion Where X is above expression
NSTM: LEFT(X,Instr(1,X & ", ", ", ")-2)

Return nothing if there is no colon in Title

IIF([Title] Not Like "*:*",Null,
LEFT(Mid([TITLE],InStr([TITLE],":")+1),Instr(1,Mid([TITLE],InStr([TITLE],":")+1)
& ", ", ", ")-2))

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

 

[John W. Vinson]

I have a memo fleld called "TITLE". I need to extract part of the field that
contains this information: TM:T-6225-CT-MMC-010/59180 (but only the part
after the ":")
It may appear at the very end (nothing would be after /59180)
It may appear in the middle of other words or it may not appear at all.

I tried using:
NSTM: Mid([TITLE],InStr([TITLE],":")+1,InStr([TITLE],", ")+1)
It only worked in some records and not others.

For example. When the TITLE was:
FUEL OIL SERVICE PUMPS CTJ12LM-250, INSTRUCTION MANUAL; MFR
TM:T-6225-CS-MMC-010/59180
My results were right: T-6225-CS-MMC-010/59180

But when the TITLE was:
PRIMARY & SECONDARY SAMPLE COOLERS, TECHNICAL MANUAL FOR HEAT TRANSFER
PRODUCTS; MFR TM:T-9511-AK-MMC-010/51657
My results wereWRONG: T-9511-AK-MMC-010/

What should I be doing in my query to get the right results?

Well, you're paying the penalty for an incorrect table design. If you're
storing multiple pieces of information (a title and a manufacturer's TM) in a
free format text field - rather than as separate fields which could easily be
concatenated - you'll have these problems!

You don't need the third argument to Mid - it's almost surely causing the
problem, because it's finding a comma which you don't need. Try

NSTM: Mid([TITLE], InStr([TITLE], "TM:") + 3)

 

[2Blessed4Stress]

I didn't design the table. This data was pulled from another database that I
have no control over. I totally agree with you but this doesn't answer my
question.

I have a memo fleld called "TITLE". I need to extract part of the field that
contains this information: TM:T-6225-CT-MMC-010/59180 (but only the part
after the ":")
It may appear at the very end (nothing would be after /59180)
It may appear in the middle of other words or it may not appear at all.

I tried using:
NSTM: Mid([TITLE],InStr([TITLE],":")+1,InStr([TITLE],", ")+1)
It only worked in some records and not others.

For example. When the TITLE was:
FUEL OIL SERVICE PUMPS CTJ12LM-250, INSTRUCTION MANUAL; MFR
TM:T-6225-CS-MMC-010/59180
My results were right: T-6225-CS-MMC-010/59180

But when the TITLE was:
PRIMARY & SECONDARY SAMPLE COOLERS, TECHNICAL MANUAL FOR HEAT TRANSFER
PRODUCTS; MFR TM:T-9511-AK-MMC-010/51657
My results wereWRONG: T-9511-AK-MMC-010/

What should I be doing in my query to get the right results?

Well, you're paying the penalty for an incorrect table design. If you're
storing multiple pieces of information (a title and a manufacturer's TM) in a
free format text field - rather than as separate fields which could easily be
concatenated - you'll have these problems!

You don't need the third argument to Mid - it's almost surely causing the
problem, because it's finding a comma which you don't need. Try

NSTM: Mid([TITLE], InStr([TITLE], "TM:") + 3)

 

[2Blessed4Stress]

Thank you very much John. This works for me.

Try building this a bit at a time.

Get the Value and anything after it
Mid([TITLE],InStr([TITLE],":")+1)

Strip of trailing portion Where X is above expression
NSTM: LEFT(X,Instr(1,X & ", ", ", ")-2)

Return nothing if there is no colon in Title

IIF([Title] Not Like "*:*",Null,
LEFT(Mid([TITLE],InStr([TITLE],":")+1),Instr(1,Mid([TITLE],InStr([TITLE],":")+1)
& ", ", ", ")-2))

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

I have a memo fleld called "TITLE". I need to extract part of the field that
contains this information: TM:T-6225-CT-MMC-010/59180 (but only the part
after the ":")
It may appear at the very end (nothing would be after /59180)
It may appear in the middle of other words or it may not appear at all.

I tried using:
NSTM: Mid([TITLE],InStr([TITLE],":")+1,InStr([TITLE],", ")+1)
It only worked in some records and not others.

For example. When the TITLE was:
FUEL OIL SERVICE PUMPS CTJ12LM-250, INSTRUCTION MANUAL; MFR
TM:T-6225-CS-MMC-010/59180
My results were right: T-6225-CS-MMC-010/59180

But when the TITLE was:
PRIMARY & SECONDARY SAMPLE COOLERS, TECHNICAL MANUAL FOR HEAT TRANSFER
PRODUCTS; MFR TM:T-9511-AK-MMC-010/51657
My results wereWRONG: T-9511-AK-MMC-010/

What should I be doing in my query to get the right results?