Merging First and Last Name fields

[Aaron]

I need some help on figuring out how to merge to fields into one in a query.
I have a table with an employee number, last name, first name, etc and i'd
like to run a query and combine the first name and last name together in a
way like "Last Name, First Name". i had read a document where you can combine
them in such a way like: [last name] & "," [first name]

Any help would be much appriciated.

 

[KARL DEWEY]

This way --
[last name] & ", " & [first name]

 

[John W. Vinson]

I need some help on figuring out how to merge to fields into one in a query.
I have a table with an employee number, last name, first name, etc and i'd
like to run a query and combine the first name and last name together in a
way like "Last Name, First Name". i had read a document where you can combine
them in such a way like: [last name] & "," [first name]

Any help would be much appriciated.

Put just that expression (well, put a blank between the comma and the second
quote to avoid getting something like "Jones,David") in a vacant Field cell in
your query. I'd suggest prefixing it with a new fieldname, otherwise Access
will just call it Expr1. Thus:

FullName: [Last name] & ", " & [First name]

 

[Aaron]

I am just learning on queries using Access so, i'm sort of dumb at this right
now. when i need a little help structuring this query and setting this up.
Not too really ask for a step by step, but just some idea on using the query
design to accomplish this. Unless someone has the SQL code for this?

Thanks for your responses.

I need some help on figuring out how to merge to fields into one in a query.
I have a table with an employee number, last name, first name, etc and i'd
like to run a query and combine the first name and last name together in a
way like "Last Name, First Name". i had read a document where you can combine
them in such a way like: [last name] & "," [first name]

Any help would be much appriciated.

Put just that expression (well, put a blank between the comma and the second
quote to avoid getting something like "Jones,David") in a vacant Field cell in
your query. I'd suggest prefixing it with a new fieldname, otherwise Access
will just call it Expr1. Thus:

FullName: [Last name] & ", " & [First name]

 

[John W. Vinson]

I am just learning on queries using Access so, i'm sort of dumb at this right
now. when i need a little help structuring this query and setting this up.
Not too really ask for a step by step, but just some idea on using the query
design to accomplish this. Unless someone has the SQL code for this?

Open a new or existing Query based on your table.

Include whatever fields you want to see.

Click into the vacant cell in the Fields row in the next column to the right
of your last selected field.

Type the literal text:

FullName: [Last Name] & ", " & [First Name]

(I'm assuming that your table field names are in fact Last Name and First
Name).

Open the query and see if it gives you the desired result.

If it does, save the query by clicking the diskette icon on the toolbar, or
accept Access' offer to save the query when you close it.

 

[Aaron]

I feel a little dumber, yet smarter in a way. That was simple enough. Thanks
a bunch for the help.This will help out alot.

Aaron.

I am just learning on queries using Access so, i'm sort of dumb at this right
now. when i need a little help structuring this query and setting this up.
Not too really ask for a step by step, but just some idea on using the query
design to accomplish this. Unless someone has the SQL code for this?

Open a new or existing Query based on your table.

Include whatever fields you want to see.

Click into the vacant cell in the Fields row in the next column to the right
of your last selected field.

Type the literal text:

FullName: [Last Name] & ", " & [First Name]

(I'm assuming that your table field names are in fact Last Name and First
Name).

Open the query and see if it gives you the desired result.

If it does, save the query by clicking the diskette icon on the toolbar, or
accept Access' offer to save the query when you close it.

 

[John W. Vinson]

I feel a little dumber, yet smarter in a way.

well... you *did* ask for step by step...

It's like a bicycle. A lot easier with practice!

 

[Sietske]

In my case, I have used

Name:[Initials] & " " & [Insertion] & " " & [Last Name]

because some Dutch names have insertions preceding the last name
(like Vincent van Gogh, Martin van Buren, Eddie van Halen).
However most Dutch names don't have an insertion.
In those cases: how do I get rid of the extra space?
I've tried to use

Name:Trim([Initials] & " " & [Insertion] & " " & [Last Name])

but this doesn't work.

This way --
[last name] & ", " & [first name]

--
KARL DEWEY
Build a little - Test a little

I need some help on figuring out how to merge to fields into one in a query.
I have a table with an employee number, last name, first name, etc and i'd
like to run a query and combine the first name and last name together in a
way like "Last Name, First Name". i had read a document where you can combine
them in such a way like: [last name] & "," [first name]

Any help would be much appriciated.

 

[Sietske]

Help is no longer needed. On
http://forums.techguy.org/business-applications/710970-solved-removing-spaces-field.html
I found someone with about the same question. This person was advised to use
LTrim and RTrim.

I've used this hint in my code, so it became

Name: [Initials] & " " & (LTrim([Insertion] & " " & [Last Name]))

And it works!

In my case, I have used

Name:[Initials] & " " & [Insertion] & " " & [Last Name]

because some Dutch names have insertions preceding the last name
(like Vincent van Gogh, Martin van Buren, Eddie van Halen).
However most Dutch names don't have an insertion.
In those cases: how do I get rid of the extra space?
I've tried to use

Name:Trim([Initials] & " " & [Insertion] & " " & [Last Name])

but this doesn't work.

This way --
[last name] & ", " & [first name]

--
KARL DEWEY
Build a little - Test a little

I need some help on figuring out how to merge to fields into one in a query.
I have a table with an employee number, last name, first name, etc and i'd
like to run a query and combine the first name and last name together in a
way like "Last Name, First Name". i had read a document where you can combine
them in such a way like: [last name] & "," [first name]

Any help would be much appriciated.

 

[John Spencer]

Assuming Insertion is null

FullName: [Initials] & (" " + [Insertion]) & " " & [Last Name]

If Insertion can be a zero length string or Null then you might want to use
FullName: [Initials] & IIF([Insertion] & ""="",""," " & [Insertion]) & " " &
[Last Name]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

In my case, I have used

Name:[Initials] & " " & [Insertion] & " " & [Last Name]

because some Dutch names have insertions preceding the last name
(like Vincent van Gogh, Martin van Buren, Eddie van Halen).
However most Dutch names don't have an insertion.
In those cases: how do I get rid of the extra space?
I've tried to use

Name:Trim([Initials] & " " & [Insertion] & " " & [Last Name])

but this doesn't work.

This way --
[last name] & ", " & [first name]

--
KARL DEWEY
Build a little - Test a little

I need some help on figuring out how to merge to fields into one in a query.
I have a table with an employee number, last name, first name, etc and i'd
like to run a query and combine the first name and last name together in a
way like "Last Name, First Name". i had read a document where you can combine
them in such a way like: [last name] & "," [first name]

Any help would be much appriciated.

 

[dgodfrey]

Is there a way to do this without using a Query? I want to merge the first
and last name of "Petitioner" into a single field called "Petitioner" so that
in my search form (using Allen Browne's search form modified) my user can
type just the last name of the petitoner and it will show all records
containing that petitioner.

Thanks,

Derek

I am just learning on queries using Access so, i'm sort of dumb at this right
now. when i need a little help structuring this query and setting this up.
Not too really ask for a step by step, but just some idea on using the query
design to accomplish this. Unless someone has the SQL code for this?

Open a new or existing Query based on your table.

Include whatever fields you want to see.

Click into the vacant cell in the Fields row in the next column to the right
of your last selected field.

Type the literal text:

FullName: [Last Name] & ", " & [First Name]

(I'm assuming that your table field names are in fact Last Name and First
Name).

Open the query and see if it gives you the desired result.

If it does, save the query by clicking the diskette icon on the toolbar, or
accept Access' offer to save the query when you close it.

 

[Allen Browne]

Presumably you are talking about this search form example:
http://allenbrowne.com/ser-62.html

You can alter the code so that a single text box looks in either field.
Something like this:

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "(([LastName] Like ""*" & _
Me.txtFilterMainName & "*"") OR ([FirstName] Like ""*" & _
Me.txtFilterMainName & "*"")) AND "
End If

The braceting is important when you mix ANDs and ORs like that.

 

[dgodfrey]

That worked as far as the search part goes, but the Petitioner's name is not
displaying in teh result list next to the Suspect's First and Last name and
ID #. It was displaying it when typing just a last name prior to my changing
the code.

Any help resolving this minor glitch would be appreciated. My other question
is, is there a way to make this search form also do a "Search as you type"
autocomplete kind of thing?

Thanks again,

Derek

Presumably you are talking about this search form example:
http://allenbrowne.com/ser-62.html

You can alter the code so that a single text box looks in either field.
Something like this:

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "(([LastName] Like ""*" & _
Me.txtFilterMainName & "*"") OR ([FirstName] Like ""*" & _
Me.txtFilterMainName & "*"")) AND "
End If

The braceting is important when you mix ANDs and ORs like that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way to do this without using a Query? I want to merge the first
and last name of "Petitioner" into a single field called "Petitioner" so
that
in my search form (using Allen Browne's search form modified) my user can
type just the last name of the petitoner and it will show all records
containing that petitioner.

 

[Allen Browne]

Not sure why your form stopped displaying the names after the search.

You can use unbound combos for the name search, but this would not be a good
idea if there are many thousands of people. If you do want combos, it would
probably be better to use 2 (one for the surname, one for the first name)
rather than a UNION query to combine both first and last names into one long
list.

If you want to the form to filter with each keystroke, you might be able to
do this:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
That's copy'n'paste stuff (no recoding needed), but works on only one field
at a time, unless you concatenate the fields in the query as previously
suggested.

You now have about half a dozen possible solutions. Hopefully you can select
the one that suits you best.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That worked as far as the search part goes, but the Petitioner's name is
not
displaying in teh result list next to the Suspect's First and Last name
and
ID #. It was displaying it when typing just a last name prior to my
changing
the code.

Any help resolving this minor glitch would be appreciated. My other
question
is, is there a way to make this search form also do a "Search as you type"
autocomplete kind of thing?

Thanks again,

Derek

Presumably you are talking about this search form example:
http://allenbrowne.com/ser-62.html

You can alter the code so that a single text box looks in either field.
Something like this:

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "(([LastName] Like ""*" & _
Me.txtFilterMainName & "*"") OR ([FirstName] Like ""*" & _
Me.txtFilterMainName & "*"")) AND "
End If

The braceting is important when you mix ANDs and ORs like that.

Is there a way to do this without using a Query? I want to merge the
first
and last name of "Petitioner" into a single field called "Petitioner"
so
that
in my search form (using Allen Browne's search form modified) my user
can
type just the last name of the petitoner and it will show all records
containing that petitioner.