extra numbers before a 0

[gmazza76]

Good afternoon

I am looking to see if I can extra the numbers before a zero in an access query?

IndexRef
1543080000011290
1543080000009170
1543080000011360
1543080000011510

I am looking to extra the number from the right prior to the 0 so if I was successful I would like an output from the query as follows
11290
9170
11360
11510

I am a bit unsure as I don't want to use left([IndexRef]) as the data I am aligning to doesn't hold the preceding 0's

thanks in advance
Gavin

 

[Micron]

Assuming your field name is Test, try this for your calculated field but substitute the number with the field name in your query which contains those numbers
Test:Mid("1543080000009170",InstrRev("1543080000009170","0",Len("1543080000009170")-1)+1)
I'm guessing the expression would look like
Mid(IndexRef,InstrRev(IndexRef,"0",Len(IndexRef)-1)+1)

EDIT - that probably could be slightly simpler if you knew that the input numbers would always be the same length (16 as you show). Perhaps
Mid(IndexRef,InstrRev(IndexRef,"0",15)+1)

 

[gmazza76]

Thanks for this.
The length of the number will always be the same if it make it easier

 

[Micron]

Then either method should work but I suggest using the first method because it should work regardless of the length. There must be at least one zero in front of the numbers that you want to extract. So if the number was 011360, the first method would return 11360 but the second would return 0.