Splitting the data in a column into to seperate columns 1

[Channah]

I have a text field that contains an ID number consisting of 12 digits. I need to break this field into two fields, the first containing the begining 7 characters, and the second, the remaining 5. If you could shoot me a quick clue on this I would really appreciate it. It is killing me. Thanks

 

[beetee]

As columns in a query in design view, you would enter:

LeftSide:Left(nz([MyField], "&quot, 7)
RightSide:Right(nz([MyField], "&quot, 5)

You could then update or whatever based on those expressions.

 

[Channah]

Thanks beetee... but when I enter this info in the update to field, i get the following message... "The expression that you entered has an invalid .(dot) or ! operator or invalid parentheses."
I think that I may be a bit confused on this one. See if some more info from me can help out so that you can give me a little better info. I am pretty new at this so please bear with me...

The 12 digit field is NCESSCH... I would like the first 7 digits in a field titled DistrictID and the last 5 in a field titled SchoolID in the same table. Lets see if that can help. Thanks again.

channah

 

[beetee]

OK, the problem is you cannot enter an expression into the Update field.

You have to enter the expression on the first line of the query design tool, (in a new column), then select the field in the UpdateTo field.

 

[Channah]

Hate to argue, but when this is entered in the first line of the query, what then becomes the Update To expression? This one may be a little over my head or I am not quite awake today, but this is not making too much sense. Any other suggestions out there?

 

[beetee]

You are completely correct, I apologize.

Put the expression in UpdateTo, and the dest field in 'Field'. That's what I get for doing it from memory.

e.g.

Field: LeftPart RightPart

Update To: Left([MyField],7) Right([MyField],5)

Obviously, field labels make little sense in an UpdateTo context, which is why the LeftPart:Left([MyField], 7) was causing trouble.

 

[Channah]

Thanks thats the answer!