I think if you have this working, I won't do any work. However, given this string it would raise a number of thoughts about the structure of the string, mentioned below.
"Payment PI0123456-FXTESTDATA12 John Doe AWF International Ltd - AC002233444 FXTESTDATA12 Expenses 789/790/791"
a) you have FX at 2 places, both formatted slightly differently.
What would happen if FX also appeared in, say the company name after John Doe ...
b) You are returning just John Doe from "John Doe AWF International Ltd" -
Are you actually getting the first 2 words, or something different?
What would happen if this was blank, or had only 1 word?
c) Is the dash before the Account Number always present?
It's just making sure that the string follows a consistent format.
The data is a fictitious version of an extract from a FrontierPay transaction CSV export file.
This is the transaction description field, which needs parsing to identify various elements not available anywhere else in the export.
a) From what I can tell the FX string data part is duplicated identically in both places.
b) The source account name (The fictitious "AWF International Ltd") never changes in each line. The "John Doe" name can be any length and any number of words.
c) Yes it is.
I have no real difficulty in parsing the string data, but I do have difficulty with Access messing with a function not working when it should, and really frustratingly it appears to work fine on another identical system.
I'm in the throws of doing a proper Office removal and reinstall, as it appears to be my computer/system at fault.
My colleague's system does not break the version
@theDBguy sent back to me earlier when he does C & R on it, so it is all pointing to some weird Office/Windows related fault on my laptop.
As it's Friday and nearly 5:00pm it's officially the weekend and after all the time I have wasted on this, it's time for a beer, a dog walk, and on-line Poker with some pals.
Thanks to everyone for their assistance and suggestions. I'll report back after the rebuild.