Fields in Value List

[edmonton]

Hi

I have a table that has 4 fields in it that I need to display in a value list.

Field1. Primary Key of the table

Field2. Title Name

Field3. Location Name

Field4. Foreign Key

I created a calculation field in the table which joins all the 4 fields. Calc field = field 2&"*"&field 3&"*"&field1&"*"&field2.I put Title Name as the first field in the calculation field since I want the calculated field to sort on Title Name.

Then I created a value list which would display this calculated field. Problem is the value list displays a lot of garbage first and then displays the good values. This garbage data is not present in the table when I look at the calculated field directly in the table.

Now if I change the calculation field to Calc field = field 1&"*"&field 2&"*"&field3&"*"&field4. THe value list displays fine now but I cannot sort on Title Name anymore. I need the value list to be sorted on Title Name.

Any help would be appreciated.

Thankyou

[bcooney]

Your value list should be created using two fields from the table , Field1 (Primary Key) and a calc field that equals Field2 & " " & Field3. Then check the option to only show values only from second field (your display field). Format this value list to be a popup menu.

I wouldn't call the field that concatenates Field2 and Field3 a foreign key, since it is simply a display field. The key that will be stored in any related table is Field1.