Extracting a invoice number out of two separate text fields
I have got 2 tables. One with the invoice records, with a field called invoice number.( 14001, 14002 etc)
In the other table i have imported all my Bank transactions an split the information in separate fields.
Now i like to make a invoice paid check. Like when a invoice is paid and there is a bank transaction imported, there is a field in the invoice record where there will be a text payed and a separate field displaying the invoice paid date.
Now i want to make a extra field in the bank account table only for the invoice number. So i can create a relation between the invoice table invoice number field and the invoice number field in the bank transactions table.
But how do i extract the invoice number out of two text fields?( sometimes the invoice number wil be in text field 1 or in text field 2).
Can anyone help me with this?