Truncate text string

Question

I have a field (InvoiceGL) that contains text strings that are our account codes. Many of them are like the following: 123.000.345.1234.1AB

What I want to do in my query is remove the "1AB" listed at the end. It's not always three characters, so I want to find the last reference to "." and return whatever is to the left of it. I've played with inStr but can't seem to get the correct syntax down. Right now I'm using:

TrimCode: Left([InvoiceGL],Right([InvoiceGL],InStr([InvoiceGL],".")-1))

But this is giving me a #Error message when I run the query. Your help is greatly appreciated!

Answer 1

Try something more like:

TrimCode: Mid([InvoiceGL],1,instrrev([InvoiceGL],".")-1)

Answer 2

Perfect. Thanks!