I have a database that I do not own, but I need to run a report on.
One of the columns is called TransactionNote and contains text like this;
"Order XXX released from PAY TYPE" and
"Order XXXXX released from PRODUCT INFORMAT"

What I need to do is split out the text in caps.
Then we would have;
"PAY TYPE"
"PRODUCT INFORMAT"

Unfortunately, these values are of variable length so I'm at a loss.
Oh, and I need this done within a query if possible.

Trust me, if this was my database I would have kept these split out in the first place.

Any ideas?

Maybe, is there a way to say "take everything after the fourth space"?

Are the words "Order" and "from" always in the string in the places you've indicated?

If so, you can use InStr to find those strings and then use the Right function to get the rest of the string.

The word "Order" is always in the same place, but the word "from" can variate based on the length of the order number (represented by XXX above).
Would InStr still work in this case?

The word "Order" is always in the same place, but the word "from" can variate based on the length of the order number (represented by XXX above).
Would InStr still work in this case?

Does it always say Order {OrderNumber} released from on each line? If so, then perhaps you can search for the end of released from and take everything after that. That would be similar to what Access Hero is suggesting.

Instr() and Right() would be required to accomplish this.

InStr finds the nth occurrence of a string within another string and returns a number. What I was thinking was that if the word "from" always precedes the string you're interested in, you could use InStr along with Right (or Mid) to find your string. Something like this:

Right(MyVariableOrFieldName,Len(MyVariableOrFieldN ame) - InStr(MyVariableOrFieldName," from ") - 5)


This works well with both samples you provided but it really depends on what the rest of your data looks like.

Gotchya,

That definately works. I added the following column in a query;
OTHHoldType: Mid([TransactionNote],((InStr(19,[TransactionNote],"from ",1))+5))
It returns everything after "from " every time.

I chose to start the InStr search from the 19th place, because an order number cannot be less than three digits, therefore the string I'm trying to parse will always be at least 19 places out. If I'm assuming correctly, this will speed up the InStr function because it is ignoring 19 places right off the bat, correct?

Just noticed the previous post. I think I used the exact opposite huh?!

Access Hero, which way would run the fastest?

Your way would probably be faster. You'd have to run a million rows through it as a test to see.

If you don't have that many rows, whatever works is usually good enough.

True that.

Thanks for your help!

NP, hope your system roll out is successful.