Please help with this Expression

[Sausagefingers]

Hi,
I am having difficulty writing the expression that will extract a portion of a string.

I'm busy looking at examples but for the life of me, I can't seem to work out the combination I need to extract the portion I want

The string follows this format: ABC_12345_1, Or ABC_123456_1

Really, I just need the middle portion (between the underscores), the rest is redundant however, because the string may be either 5 or 6 digits, I'm struggling to find the correct expression.

Can someone offer me a solution. Any help would be appreciated.

Thanks in advance

 

[Sausagefingers]

Just to flesh out my original post a little more I have so far been working along these lines:

Expr: Mid([ChargeRef],5,6)

produces the following results;

12345_ Or 123456 (from ABC_12345_1 or ABC_123456_1)

What would I need to add to the expression to get rid of the trailing underscore on the 5 digit string?

Thanks

 

[Kryst51]

Is your beginning and end always the same, I don't know how to use them, but if you can have it look to the right of the beginning string, if the beginning is always 4 places, including the underscore and the end is always 2 places including the underscore. Maybe you can create a function to pull what ever is between those two, regardless of the length of the middle function. I'll try looking around here for some examples, I know I have seen a lot of discusstion about this kind of thing.

Edit: Maybe this can help you, just googled the phrase.

 

[Kryst51]

Or maybe this might help:

Combining the functions to give more powerful string manipulation

We have seen how to use a number of string functions and can now give an example of how to use them in combination to perform some more powerful string manipulation.
Suppose we have a table that has customer names stored in it but the full name is stored in one column rather than the first name in one column and last name in the other column. Without string manipulation we would not be able to extract just the last name from the name column. The functions we have learned in this article enable us to extract the LastName from the FullName column.
SELECT Mid(FullName, Instr(FullName, ' ') + 1,
Len(FullName) - Instr(FullName, ' ')) As LastName
FROM Customers

This is from This link.

 

[MSAccessRookie]

Just to flesh out my original post a little more I have so far been working along these lines:

Expr: Mid([ChargeRef],5,6)

produces the following results;

12345_ Or 123456 (from ABC_12345_1 or ABC_123456_1)

What would I need to add to the expression to get rid of the trailing underscore on the 5 digit string?

Thanks

If you combine the Mid() Function with the Functions Instr() and InstrRev(), you should be able to get what you are looking for. Try it and post back if you have any questions.

Instr()

http://www.techonthenet.com/access/functions/string/instr.php

InstrRev()

http://www.techonthenet.com/access/functions/string/instrrev.php

 

[Sausagefingers]

Thanks for the help and advice, I'll be sure to look into the supplied links (tomorrow morning). Right now, at this side of the pond anyway, it's time to head home. My head is beginning to hurt

Thanks again, and I'll post back the results as soon as I can.