Help with query

[jjMcJoy]

I have a fieldA which is alphanumeric. I want to separate the fieldA into fieldB (leading alpha characters) and
fieldC (numeric & end alpha).

Below is a list of values in fieldA. Only the leading alpha characters need to be in fieldB. The alphas at the end
should be in fieldC. There are spaces between the alpha and the number in some values. I have tried several
things but can't get it to work. I want to do this in a query. I am not good with VB.

Can someone please help me?


ACCT201H
AE 1010
AE4120
AECN100


AECN399A
LAW 501
LAW 670G
LAW602

[pbaldy]

I don't see any pattern that can be used, so I think you're looking at a custom function that returns what you want. I'm thinking it loops the characters and uses IsNumeric to test each to find the first. Then the Left() or Mid() functions to return the desired value. I'd probably have an input parameter so the same function could return either section.