Access: split a table field?

[thing]

I have a table where one field is a 7-digit number. Actually, some of the entries are 4-digit and some are 7-digit. I would like to be able to split the 7-digit numbers into a 4-digit and a 3-digit, but can't seem to figure it out. I know I could just shorten the field length and destroy the last 3 digits, but if I could preserve them, that'd be good. Any advice?

[Skippy]

One possibility: create a new column in the table and put the first four digits of the number in this column using an IFF statement to differentiate between numbers less than 10000 (ie 4 digit) and greater than 10000 (ie 7 digit numbers).

[PK]

you also should not have much trouble using the Len([field]) funcion whihc will return the character length of the value and Mid([filed],n,m) which will return the characters starting from the n-th position for the next m.

[snafu]

This should work but depending on the number of records in the table may be a little slow...

In table design view, add two new fields. One for the first 4 digits and one for the optional last 3 digits.

In the SQL view of a new query type

UPDATE MyTable
SET MyField4Digits = Left([MyOriginalField],4),
MyField3Digits = IIf(Len([MyOriginalField])>4, Mid([MyOriginalField],5,3),Null)

This should work regardless of whether your fields are numeric or text as Access will automatically convert between the types. This will NOT work if you have decimal places, negative numbers, numbers with number of digits other than 7 or 4, and a few other circumstances not covered here.

Good luck,
snafu

[thing]

Woo-hoo! Mission accomplished. Thanks, everyone!