decrease field size but keep the end values

sherikream

Registered User.
Local time
Yesterday, 23:20
Joined
Mar 27, 2013
Messages
14
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
 
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.
 
Last edited:
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.
 
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.
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    50.1 KB · Views: 116
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.
 
The ID will be changed to non-primary key. There is a possibility that another student does have the same last 4 digits.
 
I don't think you can do that without breaking your relationships. If you do want to do this see my first post.
 
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?
 
You create an UPDATE query and UPDATE that new field's value using the appropriate formula.
 
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.
 

Users who are viewing this thread

Back
Top Bottom