Take 3 fields and make them as one

[CAD Fiend]

Hello,

I have a table (tbl_Client_Info) with three fields, First_Name,
Middle_Initial, and Last_Name. I also have another field Full_Name.
Once the user (from the form) types in the First Name, etc, I want the
query to get the 3 fields and combine them into one called Full_Name.
What type of query do is that and what is the code for that?

TIA.

Phil.

 

[Duane Hookom]

There isn't any reason to store the Full_Name if you have the parts. You can
combine them in a query or control source as needed.

 

[John Vinson]

Hello,

I have a table (tbl_Client_Info) with three fields, First_Name,
Middle_Initial, and Last_Name. I also have another field Full_Name.
Once the user (from the form) types in the First Name, etc, I want the
query to get the 3 fields and combine them into one called Full_Name.
What type of query do is that and what is the code for that?

TIA.

Phil.

Full_Name is redundant, unnecessary, and should not exist in your
table.

Wherever you want to display the full name, you can use either a
calculated field in a Query or as the control source of a Textbox:

[First_Name] & (" " + [Middle_Initial]) & " " & [Last_Name]

The extra parentheses and + operator around Middle_Initial assure that
names without a middle initial are displayed with only one blank, i.e.
like "CAD Fiend" instead of "CAD Fiend".

John W. Vinson[MVP]

 

[CAD Fiend]

John,

See my comments below:

Full_Name is redundant, unnecessary, and should not exist in your
table.

You are right. I actually meant to say that the full name is only to be
used in a query for the purposes of a mail merge.

Wherever you want to display the full name, you can use either a
calculated field in a Query or as the control source of a Textbox:

[First_Name] & (" " + [Middle_Initial]) & " " & [Last_Name]

I tried that in the select query field and it didn't work. Can you please
be more specific? Is this going to be actually an APPEND query? Where it
will take the 3 fields First_Name, Middle_Initial, Last_Name, and then put
them all in the (currently empty field Full_Name)?

 

[John Vinson]

You say below that you want to put the information into the currently
empty field Full_Name. There should not BE an empty field Full_Name.
Your table should have ONLY the first, middle, and last name fields.

You are right. I actually meant to say that the full name is only to be
used in a query for the purposes of a mail merge.

Wherever you want to display the full name, you can use either a
calculated field in a Query or as the control source of a Textbox:

[First_Name] & (" " + [Middle_Initial]) & " " & [Last_Name]

I tried that in the select query field and it didn't work. Can you please
be more specific? Is this going to be actually an APPEND query? Where it
will take the 3 fields First_Name, Middle_Initial, Last_Name, and then put
them all in the (currently empty field Full_Name)?

In a vacant Field cell in the query design grid (a simple select
query) type

Full_Name: [First_Name] & (" " + [Middle_Initial]) & " " & [Last_Name]

Open this query datasheet; you should see the full name. Did you
perhaps put the expression on the Criteria line? That's the wrong
place; it should be in a vacant column of the Fields line. If you
don't get what you want please open the query in SQL view and post it
here.

John W. Vinson[MVP]

 

[CAD Fiend]

John, see below.

You say below that you want to put the information into the currently
empty field Full_Name. There should not BE an empty field Full_Name.
Your table should have ONLY the first, middle, and last name fields.

It will, only have first, middle, and last name fields. I also just found out
that for the mail merge, I won't have to make a Full Name at all, so that ends
that.

You are right. I actually meant to say that the full name is only to be
used in a query for the purposes of a mail merge.

Wherever you want to display the full name, you can use either a
calculated field in a Query or as the control source of a Textbox:

[First_Name] & (" " + [Middle_Initial]) & " " & [Last_Name]

I tried that in the select query field and it didn't work. Can you please
be more specific? Is this going to be actually an APPEND query? Where it
will take the 3 fields First_Name, Middle_Initial, Last_Name, and then put
them all in the (currently empty field Full_Name)?

In a vacant Field cell in the query design grid (a simple select
query) type

Full_Name: [First_Name] & (" " + [Middle_Initial]) & " " & [Last_Name]

Open this query datasheet; you should see the full name. Did you
perhaps put the expression on the Criteria line?

Yes, that was my problem. Now it works fine!

That's the wrong
place; it should be in a vacant column of the Fields line. If you
don't get what you want please open the query in SQL view and post it
here.

John W. Vinson[MVP]

Thanks for your help, John!