Sorting on Last Name

[Joey]

Hi,
I'm trying to sort a report by last name, and then by first name the only
problem is that I am using a linked table for this report and the first and
last names are in the same field. Is there a way to sort by last name then
first even though the way the name is listed in the table is first name and
then last?

 

[Duane Hookom]

Is the "first and last names" in the field always consistant with firstname
then a space and then lastname like:
Joey Johnson
Duane Hookom
John Vinson
Lynn Trapp
If so, you can sort on the expression:
=Mid([The Same Field], Instr([The Same Field]," ")-1)
If however you have a value like
Duane K Hookom
you may have to do some fancy expression work to find the last space.

If you can't figure this out, come back with a real field name as well as
sampling of data.

 

[Joey]

yes, always first name space last name. I'm just not clear on where to put
this formula in a report, I only know how to sort using the grouping and
sorting menu and that menu only seems to allow me to use fields from the
table.

The field name is Client Name.

Thanks

Is the "first and last names" in the field always consistant with firstname
then a space and then lastname like:
Joey Johnson
Duane Hookom
John Vinson
Lynn Trapp
If so, you can sort on the expression:
=Mid([The Same Field], Instr([The Same Field]," ")-1)
If however you have a value like
Duane K Hookom
you may have to do some fancy expression work to find the last space.

If you can't figure this out, come back with a real field name as well as
sampling of data.


--
Duane Hookom
Microsoft Access MVP

Hi,
I'm trying to sort a report by last name, and then by first name the only
problem is that I am using a linked table for this report and the first and
last names are in the same field. Is there a way to sort by last name then
first even though the way the name is listed in the table is first name and
then last?

 

[Marshall Barton]

I'm trying to sort a report by last name, and then by first name the only
problem is that I am using a linked table for this report and the first and
last names are in the same field. Is there a way to sort by last name then
first even though the way the name is listed in the table is first name and
then last?

No, not in any reliable way. A person's names are whatever
that person says they are. Simple names like Joe Blow can
be handed by parsing out the space:

=Mid(fullname, InStr(fullname, " ") +1) & ", " &
Left(fullname, InStr(fullname, " ") - 1)

but that won't work for names like Oscar de la Hoya or Mary
Smith Von Kraus.

 

[Joey]

The names are not that complicated just first space last. I just don't know
where to enter this formula in the report.

 

[Duane Hookom]

Enter the expression (beginning with =) in the Field/Expression column in
your Sorting and Grouping dialog. You are not limited to only field names.

--
Duane Hookom
Microsoft Access MVP

yes, always first name space last name. I'm just not clear on where to put
this formula in a report, I only know how to sort using the grouping and
sorting menu and that menu only seems to allow me to use fields from the
table.

The field name is Client Name.

Thanks

Is the "first and last names" in the field always consistant with firstname
then a space and then lastname like:
Joey Johnson
Duane Hookom
John Vinson
Lynn Trapp
If so, you can sort on the expression:
=Mid([The Same Field], Instr([The Same Field]," ")-1)
If however you have a value like
Duane K Hookom
you may have to do some fancy expression work to find the last space.

If you can't figure this out, come back with a real field name as well as
sampling of data.


--
Duane Hookom
Microsoft Access MVP

Hi,
I'm trying to sort a report by last name, and then by first name the only
problem is that I am using a linked table for this report and the first and
last names are in the same field. Is there a way to sort by last name then
first even though the way the name is listed in the table is first name and
then last?

 

[Marshall Barton]

In the Sorting and Gruping window (View menu).

I see that Doug has suggested the same thing so I'll leave
it to him if you have any more difficulties.

 

[Joey]

For some reason it's not sorting. The field that its being grouped by is
called Cardholder Name. The data is listed as First name space Last Name,
for example
John Smith. In the grouping and sorting menu for the form I entered
=Mid([Cardholder Name], Instr([Cardholder Name]," ")-1)

Can you figure out what is wrong?

Enter the expression (beginning with =) in the Field/Expression column in
your Sorting and Grouping dialog. You are not limited to only field names.

--
Duane Hookom
Microsoft Access MVP

yes, always first name space last name. I'm just not clear on where to put
this formula in a report, I only know how to sort using the grouping and
sorting menu and that menu only seems to allow me to use fields from the
table.

The field name is Client Name.

Thanks

Is the "first and last names" in the field always consistant with firstname
then a space and then lastname like:
Joey Johnson
Duane Hookom
John Vinson
Lynn Trapp
If so, you can sort on the expression:
=Mid([The Same Field], Instr([The Same Field]," ")-1)
If however you have a value like
Duane K Hookom
you may have to do some fancy expression work to find the last space.

If you can't figure this out, come back with a real field name as well as
sampling of data.


--
Duane Hookom
Microsoft Access MVP


:

Hi,
I'm trying to sort a report by last name, and then by first name the only
problem is that I am using a linked table for this report and the first and
last names are in the same field. Is there a way to sort by last name then
first even though the way the name is listed in the table is first name and
then last?

 

[Duane Hookom]

Add a text box to the group header section with a control source of:
=Mid([Cardholder Name], Instr([Cardholder Name]," ")-1)
Is this the only sorting and grouping level in your report?
--
Duane Hookom
Microsoft Access MVP

For some reason it's not sorting. The field that its being grouped by is
called Cardholder Name. The data is listed as First name space Last Name,
for example
John Smith. In the grouping and sorting menu for the form I entered
=Mid([Cardholder Name], Instr([Cardholder Name]," ")-1)

Can you figure out what is wrong?

Enter the expression (beginning with =) in the Field/Expression column in
your Sorting and Grouping dialog. You are not limited to only field names.

--
Duane Hookom
Microsoft Access MVP

yes, always first name space last name. I'm just not clear on where to put
this formula in a report, I only know how to sort using the grouping and
sorting menu and that menu only seems to allow me to use fields from the
table.

The field name is Client Name.

Thanks

:

Is the "first and last names" in the field always consistant with firstname
then a space and then lastname like:
Joey Johnson
Duane Hookom
John Vinson
Lynn Trapp
If so, you can sort on the expression:
=Mid([The Same Field], Instr([The Same Field]," ")-1)
If however you have a value like
Duane K Hookom
you may have to do some fancy expression work to find the last space.

If you can't figure this out, come back with a real field name as well as
sampling of data.


--
Duane Hookom
Microsoft Access MVP


:

Hi,
I'm trying to sort a report by last name, and then by first name the only
problem is that I am using a linked table for this report and the first and
last names are in the same field. Is there a way to sort by last name then
first even though the way the name is listed in the table is first name and
then last?

 

[Joey]

okay, so now I see why the sort is not working. I added the field and I see
that when I run the report, it is coming up with the last letter of the first
name space and then the last name. for example for John Smith it returns "N
Smith". It seems to be sorting on the last letter of the first name. How
can I get this to sort by Last then First Name?

Add a text box to the group header section with a control source of:
=Mid([Cardholder Name], Instr([Cardholder Name]," ")-1)
Is this the only sorting and grouping level in your report?
--
Duane Hookom
Microsoft Access MVP

For some reason it's not sorting. The field that its being grouped by is
called Cardholder Name. The data is listed as First name space Last Name,
for example
John Smith. In the grouping and sorting menu for the form I entered
=Mid([Cardholder Name], Instr([Cardholder Name]," ")-1)

Can you figure out what is wrong?

Enter the expression (beginning with =) in the Field/Expression column in
your Sorting and Grouping dialog. You are not limited to only field names.

--
Duane Hookom
Microsoft Access MVP


:

yes, always first name space last name. I'm just not clear on where to put
this formula in a report, I only know how to sort using the grouping and
sorting menu and that menu only seems to allow me to use fields from the
table.

The field name is Client Name.

Thanks

:

Is the "first and last names" in the field always consistant with firstname
then a space and then lastname like:
Joey Johnson
Duane Hookom
John Vinson
Lynn Trapp
If so, you can sort on the expression:
=Mid([The Same Field], Instr([The Same Field]," ")-1)
If however you have a value like
Duane K Hookom
you may have to do some fancy expression work to find the last space.

If you can't figure this out, come back with a real field name as well as
sampling of data.


--
Duane Hookom
Microsoft Access MVP


:

Hi,
I'm trying to sort a report by last name, and then by first name the only
problem is that I am using a linked table for this report and the first and
last names are in the same field. Is there a way to sort by last name then
first even though the way the name is listed in the table is first name and
then last?

 

[Duane Hookom]

If it is returning a string beginning two characters too far to the left,
correct the expression like:
=Mid([Cardholder Name], Instr([Cardholder Name]," ")+1)

--
Duane Hookom
Microsoft Access MVP

okay, so now I see why the sort is not working. I added the field and I see
that when I run the report, it is coming up with the last letter of the first
name space and then the last name. for example for John Smith it returns "N
Smith". It seems to be sorting on the last letter of the first name. How
can I get this to sort by Last then First Name?

Add a text box to the group header section with a control source of:
=Mid([Cardholder Name], Instr([Cardholder Name]," ")-1)
Is this the only sorting and grouping level in your report?
--
Duane Hookom
Microsoft Access MVP

For some reason it's not sorting. The field that its being grouped by is
called Cardholder Name. The data is listed as First name space Last Name,
for example
John Smith. In the grouping and sorting menu for the form I entered
=Mid([Cardholder Name], Instr([Cardholder Name]," ")-1)

Can you figure out what is wrong?


:

Enter the expression (beginning with =) in the Field/Expression column in
your Sorting and Grouping dialog. You are not limited to only field names.

--
Duane Hookom
Microsoft Access MVP


:

yes, always first name space last name. I'm just not clear on where to put
this formula in a report, I only know how to sort using the grouping and
sorting menu and that menu only seems to allow me to use fields from the
table.

The field name is Client Name.

Thanks

:

Is the "first and last names" in the field always consistant with firstname
then a space and then lastname like:
Joey Johnson
Duane Hookom
John Vinson
Lynn Trapp
If so, you can sort on the expression:
=Mid([The Same Field], Instr([The Same Field]," ")-1)
If however you have a value like
Duane K Hookom
you may have to do some fancy expression work to find the last space.

If you can't figure this out, come back with a real field name as well as
sampling of data.


--
Duane Hookom
Microsoft Access MVP


:

Hi,
I'm trying to sort a report by last name, and then by first name the only
problem is that I am using a linked table for this report and the first and
last names are in the same field. Is there a way to sort by last name then
first even though the way the name is listed in the table is first name and
then last?