Calculated field in ACCESS IIF

In access I have a column, called ID number and a column called Short ID. The problem is that the ID number has changed over time- it used to contain letters, numbers, and dashes and now it only contains numbers.

For the new IDs the formula: Right([ID number],6) gives me what I need (the last 6 characters)

but for the old IDs id only like the last 4 characters Right([ID number],4)

Is there a formula I could write so that anytime the ID number contains a dash "-" it gives the the last 4 characters and anytime it doesn't it gives me the last 6?

https://stackoverflow.com/questions/66037974/calculated-field-in-access-iif February 04, 2021 at 09:03AM