Grab letters after a particular string

[Rustin788]

Hey,

I was hoping someone might be able to help me with this. Below you will see examples of our our system combines some things in the database, specifically are GL database.

Jnl:19952 Wh:03 Receipt Stk:10BC

Jnl:19541 Wh:03 Receipt Stk:760PO

Jnl:4862 Wh:84 Issue Stk:11LB

Jnl:19481 Wh:50 Issue Stk:23LBS-1811

Jnl:12485 Wh:50 Issue Stk:LAB30

Each line is contained in one field. I would like to be to easily group and compare similar items, the item names are what is located after "Stk:" but I'm not sure how to separate that out. Now I know I can easily do just a Like criteria to grab one single item but I would like to pull everything out and throw it into a pivot table so I could for instance see the total amount issued and total amount received for 10BC over several periods. Just looking at one GL-Codes's line items for July has 26741 lines so anything manual is not an option. They all vary in amount of characters so I don't believe a basic trim would work.

Thanks for any advice.

[Bulzie]

If in a query:
FieldStk: Mid([YourField], InstrRev([YourField],":")+1)

[Rustin788]

Awesome, that worked! Thanks!

Let me make sure I understand how it works so I can use it for future issues as well. The InstrRev function is finding the last instance of a semicolon by working backwards through the field and then the Mid function is using that as the start position. The +1 is moving it one position away from the semicolon and grabs everything after that. Does that sound right?

Also looks like I could do the same thing with the Jnl by using InStr. Is there also a way I could do the same thing with the WH value?

[Bulzie]

Yep you got it right as to how that code works. Yes you can use combinations of functions to usually get what you need. If the value you are looking for is always a fixed length, then it is easier.

Basically can use the the following or possibly other functions to get what you need:

Instr or InstrRev functions to get the position of a character or group of characters, then add or subtract values depending on where the string is you need
Left, Mid, Right functions to get at the specific data in combination with the Instr, InstrRev

Jnl:19952 Wh:03 Receipt Stk:10BC
Mid ( text, start_position, number_of_characters )

To get Jnl, you need to know where to start and end. So would be something like Mid([Yourfield], Instr([Yourfield], ":")+1,(Instr([Yourfield], ":")+1 - Instr([Yourfield], " W")-1))

Notice last part starts at space before the W