Dropping the last 8 characters in a string

[lis0122]

I have a field in a table that has data in this general format

AAAA_BB_CCC_DECLINE
AAAA_BBBB_CC_CONTACT
AAA_BB_CCC_DECLINE
etc

The A's, B's and C's are always different character lengths, but the
string always ends in either "_DECLINE" or "_CONTACT" (the last 8
characters).

How do I write a query using Right(), Left(), or Instr() functions to
parse out the last 8 characters and return what's left (eg AAAA_B_CCC)?
I keep playing around with this and I feel like I'm getting so close
but I just can't figure it out.

Thanks,
Lisa

 

[Guest]

Try using Left with Len that return the amount of chr in the string

Left([FieldName],Len(FieldName])-8)

 

[Tom Ellison]

Dear Lisa:

MID(Str, INSTR(INSTR(INSTR(Str, "_") + 1, Str, "_") + 1, Str, "_") + 1)

Replace Str above with the column name.

Tom Ellison

 

[John Spencer]

Left([Tablename].[FieldName], Len([Tablename].[FieldName])-8)

If [Tablename].[FieldName] is ever null or less than 8 characters long, you
will get an error, so you might want to check the field's length

IIF(Len([Tablename].[FieldName] & "")>8,
Left([Tablename].[FieldName],Len([Tablename].[FieldName])-8),[Tablename].[FieldName])

 

[MH]

Left([FieldName],Len([FieldName])-8)

 

[Tom Ellison]

Everyone else seems to have interpreted "parse out" to mean "remove, drop,
eliminate". I took it to mean this was all that is to remain.

I wrote what I have so it does not depend on the lengths of any of the 4
parts. It locates the position of the third underscore. That may be more
dynamic than is needed.

An alternative would be:

LEFT(Str, INSTR(INSTR(INSTR(Str, "_") + 1, Str, "_") + 1, Str, "_") - 1)

 

[lis0122]

Great, got it to work. Thanks all!

 

[MH]

Hey, look at this, just because Tom posted twice he gets all the credit!
<g>

MH