I have a database that has a "Code" field (which is short text) within the code is the two character country code, which can be at any length within the "Code" field. I have another field in the database with the "Country" (also short text) that would be found in the "Code" field. Within the "Code" field after each "Country" two digest is the invoice number. I need this separated into a new field I've called "Invoice" (also short text). I've tried many Right Function formulas but I keep getting just the ending numbers for the invoice, I need the full invoice number.
FindCountry: InStr([Code],[Country]) This gives me the starting character for each country. I know that each country is two digest.
Below is what I've tried so far:
Invoice: Right([Code],Len(FindCountry])-InStr([Code],-))
Invoice: IIf(InStr([Code],[Country]),Trim(Right([Code],InStr([Code],[Country])-1)),"")
Ex Code: Invoice that I'm expecting:
FRBCJM12345 12345
FRBCFRBIAT71-5897 BIAT71-5897
FRBCCO-NB-01090 -NB-01090
I bolded the country within the code for emphasis. Please let me know how to get the full invoice number to the right of each country code. Thanks.