Dividing a String in a query 1

[khwaja]

I have following data in a table and would like to split it into two field so that I have a field with all alpha and the other with numerals (last two digits)only. Could someone kindly give me a formuala to achieve this?

Type
BABY ACCESSORIES 8
BOOKS 4
CAMPING 1
CANDLES 1
CARDS & WRAP 26
COMMUNICATIONS 11
COOKWARE 5
COSMETICS 4
DIGITAL & FILM 31
DINNERWARE 2
ELECTRICAL 4
FAMILY APPAREL & ACCESSORIES 5
FAMILY FOOTWEAR 2
FAMILY SOCKS 3
FAMILY UNDERWEAR 16

Cheers

AK

Note: Using Access 97 for back end A2002 for front end.

 

[PHV]

A starting point:
SELECT Type, Trim(Left([Type], Len(RTrim([Type]))-2)) AS Alpha, Val(Right(RTrim([Type]),2)) AS Num
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[khwaja]

Mate, you are simply amazing. You pulled it off in such a simple manner. Much appreciate your help.

Cheers

AK

Note: Using Access 97 for back end A2002 for front end.