Extracting Data

[Guest]

I have a field I am trying to extract data in a query from and cannot get the
data to split right. One is 1 or 2 alpha characters then 1 or 2 numbers
(SP1, NP12, etc) and I need the numeric charaters split out.

I know it is simple but cannot get it split. Thanks.

 

[Allen Browne]

Assuming Access 2000 or later, and that the field is not Null, you can get
the number of numeric digits with:
Len(CStr(Val(StrReverse([Field1]))))

The number if alpha characters is therefore:
Len([Field1]) - Len(CStr(Val(StrReverse([Field1]))))

The alpha part is therefore:
Left([Field1], Len([Field1]) - Len(CStr(Val(StrReverse([Field1])))))
and the numeric part is:
Mid([Field1], Len([Field1]) - Len(CStr(Val(StrReverse([Field1])))) + 1)