Sir & Kevin,
I apologize. There should be no quotation marks around the -1 in the IIf statement.
Kevin, I built a table with the five fields you mentioned and then used the form wizard to build a form. I entered a variety of values for ten records with autonumber IDs 1 - 10.
Then I went to the database window, selected the queries tab; 'New'; 'Design'. I added the table, closed the add window, selected the five fields and dragged them into the grid.
On the top line of the grid where it read Trip_A, I replaced it with
Code:
A: IIF([Trip_A] = -1, " A ", "")
I did the same thing in the other columns for B, C, and D.
Note: I used a space before and after the 'A' (
et al) because using a comma as a separater would involve checking if each letter was first or last in the string.
In the next open column of the query, I typed the following in the field line:
Code:
Trips: [A] & [B] & [C] & [D]
.
When I look at the data view, I see the strings as they should be.
I'm not sure how you have your relationships set up or how to link the string to another table, but I built another query based on this query with the two fields: Trip_ID and Trips. On the query properties, I changed the Unique records property to "Yes" to eliminate some redundancy.
I apologize if my explanations have been unclear and I hope this is helpful.
John
PS: Be sure to put the expressions in the top "Field" line of the QBE grid, not the criteria line.