If I have a field of ssn's in the form of 123-45-6789 that have already been entered, and only want to keep the 6789 (last four digits) how can I redefine the field to get rid of the rest?

I wouldn't get rid of the rest. I would define a new calc field with:

Right ( ssn ; 4 )

You realize you may end up with duplicates ... many people could have the same 'last four'?

That didn't work for me. You may need to be more specific. Doesn't mater if duplicates exist.

If you really don't want to save the whole ssn, make your field a text field.

1) Open up the Define Database screen and locate the field.

2) Double click on the field to get its options screen.

3) Set the Auto-Enter settings to "Calculated Value".

4) For the calculation, set it to the below:

Right (yourssnfieldname;4)

5) Once you're back at the Auto-Enter settings screen, make sure "Do not replace existing value of field (if any)" is unchecked.

6) Save your changes.

Now anything that gets entered into the SSN field will be truncated to the last four digits as soon as you exit the field.

Thanks for explaining, coconutt2000.

That didn't work for me. You may need to be more specific.

And it would be helpful if you would as well. Saying "that didn't work' tells us NOTHING. You need to tell us exactly what you tried so we can help you further. So now, if I understand correctly and if you followed the prior post's suggestion, you have just wiped out your social security numbers?

I suggested a SECOND field for a reason. Your social security numbers should be preserved.

LaRetta

I would never try anyones ideas on an original, I always make a copy. And none of the suggestions wiped out the old data for some reason. Your suggestions were good for new data but not for the 900 entries I already had. I finally exported them and then reimported them into a duplicated field (with the calc.) and that formatted them.

Edited April 16, 200619 yr by Guest

It could have been handled much simpler if you would have created a new calc (instead of a standard field) xor told me what wasn't working, but I'm glad it worked out for you. :wink2:

Since what I described was for a lookup field, it wouldn't affect data that had already been typed into the field. Look up fields evaluate after something has been changed or entered for the first time. They don't constantly evaluate their contents.

Glad you got what you wanted though, but LaRetta is right. It is often better to keep a copy of the original data.

If you used a regular calculation field, you could overlay the calc field over the ssn field and make it a button that puts the cursor in the text field. When you type the ssn number into the ssn field, the calc field would then use the function Right(ssnfield;4) to return the last four digits of the number. Because the calc field overlays the text field, the text field is not visible, but when you click on the calc button and it transfers the insert cursor to the text field, the text field comes to the front. Once you hit enter, the text field disappears to the back, and voila... The calc field is in the front displaying only the last four digits.

If in the future you discover you need to switch to using the whole ssn or maybe even the last 5 digits, or 6 digits, switching the calculation in the calc field will make the change across all your records.

coconutt2000, just to clarify ...

Your description (see your point #3 above) wasn't a Lookup. It was an Auto-Enter (Replace) by calculation on a standard field. Lookups are entirely different although the results can be similar. :wink2:

Oh yeah... One of those "duh" moments.

For some reason, when I think of lookup I include fields that use calculations to alter the data that's been input.

(I think it is because auto entry fields that use calculations are probably a parent class for the lookup fields. But that's an excellent point. I was misusing the terminology.)