IIf MID RIGHT Query Help

[jmc5319]

I am a beginner and have tried researching how to build this query but haven't found a source that showed me how to create a combined IFF statement that uses the LEFT and MID function on certain number ranges for the same field (column). I have an ID field (Original ID) that blends together different data sources and I want to build a query that creates a new field but only with the digits from the IDs depending on which range the ID falls into.



In English...

IDs between 100000 and 109999, returns the last 4 digits (For example, ID 109999 would return as 9999)
IDs between 210000-21999, returns the middle 2 digits (For example, ID 219800 would return 98)
IDs between 310000-31999, returns the middle 2 digits (For example, ID 319800 would return 98)
Then if any of the IDs do not fall in those ranges, return all 6 digits

In SQL I got this far with no success..

SELECT * FROM tblID

IIF([Original ID] >="100000" and <="109999" ,RIGHT([Original ID],3,4),IIF([Original ID] >="210000" and <="219999",MID([Original ID],3,2),IIF([Original ID] >="310000" and <="319999",MID([Original ID],3,2)));

Any guidance and support would be very much appreciated!

[June7]

Is [Original ID] a number type field? If so, don't use quote marks around the criteria. If you move Right only 3 places, how can 4 be returned? There can be values less than 100000?

SELECT *, IIf([Original ID]>=100000 AND [Original ID]<=109999, MID([Original ID],3), IIf([Original ID]<=319999, MID([Original ID],3,2), [Original ID])) AS NewNum FROM tblID;