Combining Fields in Table?

[RIck5150]

I have a simple form that separates the salutation, first name, middle initial, last name and suffix into different fields for sorting reasons.

In the form itself I have a unbound field with a formula that joins all the names together so they appear as one complete name.

Is there a way I can use the complete name as it's own field in a dropdown box on another form?

For instance,

Field 1: "Mr."
Field 2: "Joe"
Field 3: "J."
Field 4: "Johnson"
Field 5: "Jr."

Unbound field returns "Mr. Joe J. Johnson Jr."

How can I use "Mr. Joe J. Johnson Jr." in a dropdown box to choose from common names so I do not have to re-enter them manually?

 

[Pat Hartman]

In your query, you can add a calculated column.

Select fldA, fldB, fld1 & " " & fld2 & " " & fld3 & " " & fld4 & " " & fld5 & " " as fldC, fldD, ...

 

[RIck5150]

In your query, you can add a calculated column.

Select fldA, fldB, fld1 & " " & fld2 & " " & fld3 & " " & fld4 & " " & fld5 & " " as fldC, fldD, ...

Sorry, I do not understand how to do this. I have not idea how to add a calculated query.

For my form, I have a field named "NAME" that is:

=[SALUTATION] & " " & [FIRST NAME] & " " & [MIDDLE INITIAL/NAME] & " " & [LAST NAME] & " " & [SUFFIX]

and this returns the complete name. Can I use this same expression in the query? I cannot seem to get it to work...

 

[RIck5150]

Okay, I think I have the calculated query done accurately as it returns the correct response when running the query. When I add that field to my form, it yields a blank dropdown box.

 

[Rabbie]

Not a good idea to have NAME as a field n your form as Name is a reserved word. I would reccomend changing it to ClientName or CustomerName or whatever.

 

[RIck5150]

I changed the "NAME" field to "FULLNAME."

If this cannot be done in the manner abover, is there a better way to combine these fields and still have them appear in a dropdown box for easy selection? I am completely stumped...

 

[Pat Hartman]

If you want them to appear in a combobox, you need to put the expression in a query. Create a query that selects all the columns you need using the query designer. Switch to SQL view and paste the SQL string here. Someone will show you how to modify the query to do what you want.

 

[RIck5150]

Thanks Pat, here is the SQL view:

SELECT [Costume Designer Table].Prefix, [Costume Designer Table].FirstName, [Costume Designer Table].MiddleInitial, [Costume Designer Table].LastName, [Costume Designer Table].Suffix, [Costume Designer Table].Prefix & " " & [FirstName] & " " & [MiddleInitial] & " " & [LastName] & " " & [SUFFIX] AS Exp1
FROM [Costume Designer Table]
GROUP BY [Costume Designer Table].Prefix, [Costume Designer Table].FirstName, [Costume Designer Table].MiddleInitial, [Costume Designer Table].LastName, [Costume Designer Table].Suffix, [Costume Designer Table].Prefix & " " & [FirstName] & " " & [MiddleInitial] & " " & [LastName] & " " & [SUFFIX];

 

[Pat Hartman]

I changed the query because you need to include the primary key field so that you have a unique identifier to select on. I also removed the group by and changed it to an order by. This is going to produce a list ordered by firstname, middle intial, and then last name. Just rearrange the fields if you prefer last name order. You will end up with two columns for your combo - the PK and the concatenated fullname. Make sure the column count = 2, the bound column = 1, and the column widths are 0",2" This will hide the pk so all you see is the name but it will store the pk which is the correct field to store.

SELECT [Costume Designer Table].[your primary key field], [FirstName] & " " & [MiddleInitial] & " " & [LastName] & " " & [SUFFIX] AS FullName
FROM [Costume Designer Table]
order BY [FirstName] & " " & [MiddleInitial] & " " & [LastName] & " " & [SUFFIX];

 

[RIck5150]

Brilliant! It works perfectly. Last question for this. Can I sort by last name, but display it exactly as you have done? Thank you so much for this!

 

[Pat Hartman]

No. The recordset should be sorted as the name is displayed otherwise the type and expand function will not work correctly.