in MS Access I have a column "Test":

I want to display a new column, where everything before the first letter is removed, like:

I tried using the mid() and InStr() function as followed:

Select

mid([Test], InStr([Test], '[az-AZ]')) as FilteredColumn

From TableA;

this should've returned the first occurence of a letter via the InStr() function and use that position as starting point for the mid() function.

This doesn't work unfortunately. I tried it with left() instead of mid() as well, but no success.

CodePudding user response：

You didn't describe the error but I am guessing you were getting Invalid procedure call or argument error. What happens is this:

• The InStr function can search for a string, not a pattern
  • So it returns 0 when it cannot find the string [az-AZ]
• The Mid function expects a number greater than 0 and thus throws an error

The workaround is to look for space character in the string, if it is present then extract the portion after it:

SELECT IIf(InStr(Test, ' ') > 0, Mid(Test, InStr(Test, ' ')   1), Test)
FROM ...