That field should be an auto-enter text field, not a calculation. It only references it's own table, so there's no reason to have it as a calc type.
Also, there is an option to sort by the second field only in a two part value list, you need to use that otherwise it will sort by the first ID field you have.
Sorry, I'm still new to this and a little confused.
I set the Field to TEXT and then go to Auto-Enter and then what? I thought that I had to click on calculated value... am I supposed to use Looked-up value?
Change to text field
Under field options (double click the field) check off “calculation”
In the calculation field use:
If ( not isempty( LastName & FirstName ) ; LastName & “, “ & FirstName ; ID )
Uncheck the box that says “do not replace contents if value exists”
This will replace the contents of your text field with LastName, FirstName whenever first or last name changes, and first and last name are both entered. Otherwise it just returns the ID.
Thank you, it worked... I hope to be as knowledgeable as you guys some time in the future.
Just wondering your reasons for checking for content in the name fields and if empty returning the ID instead?
Sometimes imported data is missing fields, and if you have the same blank value for a number of records, it can mess with a two-part value list since essentially you have a duplicate blank value in the second field for any number of records.
In this case too, I usually add some sort of indicator for people with the exact same name (EG street name and zip code).