Search code examples
sqlms-access-2016

Trim, Left, Right, Mid?

I have list of strings in a table that look something like this

John Doe 2015 Toyota
Bob Smith 2017 Dodge
Sally Simms 2015 Ford

I want to strip off everything before the first instance of a number or two Spaces left of the end. I want to be left with:

2015 Toyota
2017 Dodge
2015 Ford

I have tried all combination of Left, Right and Mid with variation of Instr and InsrtRev. Can someone please help me with the code for my query. Here is my last attempt and it does not work:

Vehicle: Mid([My_Field],InStrRev(InStrRev(1,[My_Field]," ")+1,[My_Field]," "))
Solution

  • You can use this modified expression to retract the last two words:

    Vehicle: Mid([My_Field],1+InStrRev([My_Field]," ",InStrRev([My_Field]," ")-1))

    To strip the last two words:

    Vehicle: Mid([My_Field],1,InStrRev([My_Field]," ",InStrRev([My_Field]," ")-1)-1)