decrease field size but keep the end values

[sherikream]

Hi, I have this database where I want to change the field size from 9 to 4 but I want to keep the last 4 digits not the first 4 digits. Is there a way to make that change?

- Thanks all in advance

 

[plog]

a field size of 9 implies text, but you want to keep the last 4 digits. Is this data numeric or text?

In either case I would create a new field in your table with the correct data type, then I would update that new field based on the existing data you have in each record. If this data is text, you use the Mid Function (http://www.techonthenet.com/access/functions/string/mid.php) if numeric you can use the MOD function (http://office.microsoft.com/en-us/access-help/HV080756430.aspx).

 

[sherikream]

The data is numeric but set as text data type. Would that need the Mod or Mid function? Within each record are more records. For example The field is customer ID, and when expanded, there are customer order #'s.

Where do I enter the function? So this function will take the old current field's data and duplicate it into the new field with my desired length and keeping the end values?
I am not too familiar with access, but I need to modify a database.

 

[plog]

The field is customer ID, and when expanded, there are customer order #'s.

What does that mean exactly? How are you 'expanding' this field? If you have a field that contains more than one discrete piece of information, you need to break it out into multiple fields. For example, you shouldn't have a field called 'FullName', with 'John J. Smith' in it. Instead you should have 3 fields (FirstName, MiddleName, LastName) each containing the appropriate data.

 

[sherikream]

I've uploaded a photo of an example. The studentID is where want to decrease the field size and only keep the last 4 digits. Within the student ID are all the appeals that the student has submitted.

 

[plog]

You probably can't change the ID field without breaking that link to the other table. Also, is the Student ID the Social Security Number? Are the last 4 digits of the ID unique? That is, could there be a student with these ID numbers in your data:

StudentID
123-45-6789
321-54-6789
444-88-6789

If that's true, then you definitely can't do it because your ID will no longer be unique.

 

[sherikream]

The ID will be changed to non-primary key. There is a possibility that another student does have the same last 4 digits.

 

[plog]

I don't think you can do that without breaking your relationships. If you do want to do this see my first post.

 

[sherikream]

yes, I plan to try breaking the relationship first.

Where do I enter the function? The function will take the current field's data and duplicate it into the new field with my desired length and keeping the end values?

 

[plog]

You create an UPDATE query and UPDATE that new field's value using the appropriate formula.

 

[gemma-the-husky]

if you want to do this, do the data conversion first

update the field to right(fieldname,4)

THEN you can convert the size if the text field, if you must.

There is no need to change the size though, anyway.