Changing number to First & Last Name on a report

[Guest]

I have an issues database where I have a field called ‘Responsibility’ that
is set as a Number format and then the following Lookup is set to return the
First & Last Name of the person who is responsible for the issue:

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] AS
Expr1 FROM Contacts ORDER BY Contacts.[Last Name];

This works fine in the forms and when creating the reports I have the
ControlSource set as the ‘Responsibility’ field. However, I know this is
wrong as it returns the Number allocated to the person responsible rather
than their first and last name. Can anyone suggest the correct expression
that I should have in the ControlSource to return the name rather than a
meaningless number?

 

[Marshall Barton]

I have an issues database where I have a field called ‘Responsibility’ that
is set as a Number format and then the following Lookup is set to return the
First & Last Name of the person who is responsible for the issue:

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] AS
Expr1 FROM Contacts ORDER BY Contacts.[Last Name];

This works fine in the forms and when creating the reports I have the
ControlSource set as the ‘Responsibility’ field. However, I know this is
wrong as it returns the Number allocated to the person responsible rather
than their first and last name. Can anyone suggest the correct expression
that I should have in the ControlSource to return the name rather than a
meaningless number?

The text box (and Sorting and Grouping) would just use the
name of the field. In this case it's Expr1. Because that
name provides no clues to its use, you should change the
automatically created name to something more meaningful:

SELECT Contacts.ID, Contacts.[First Name] & " " &
Contacts.[Last Name] AS FullName
FROM Contacts
ORDER BY Contacts.[Last Name]

 

[Guest]

I have made the change to the table as suggested but the reports continue to
return the autonumber rather than the name. What do I need to do to the
controlsource? I have attempted adding the expression below but it doesn't
work.

I have an issues database where I have a field called ‘Responsibility’ that
is set as a Number format and then the following Lookup is set to return the
First & Last Name of the person who is responsible for the issue:

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] AS
Expr1 FROM Contacts ORDER BY Contacts.[Last Name];

This works fine in the forms and when creating the reports I have the
ControlSource set as the ‘Responsibility’ field. However, I know this is
wrong as it returns the Number allocated to the person responsible rather
than their first and last name. Can anyone suggest the correct expression
that I should have in the ControlSource to return the name rather than a
meaningless number?

The text box (and Sorting and Grouping) would just use the
name of the field. In this case it's Expr1. Because that
name provides no clues to its use, you should change the
automatically created name to something more meaningful:

SELECT Contacts.ID, Contacts.[First Name] & " " &
Contacts.[Last Name] AS FullName
FROM Contacts
ORDER BY Contacts.[Last Name]

 

[Marshall Barton]

Let's back up a little. I thought (incorrectly?) that the
query is the report's record source. But the query does not
have a Responsibility field so I must have gotten that
wrong.

If the report is bound to a different table/query that does
not include the contact's name, then I think you should join
the Contacts table to the Issue(?) table:

SELECT Issues.*, Contacts.[First Name] & " " &
Contacts.[Last Name] AS Fullname
FROM Issues INNER JOIN Contacts
ON Issues.Responsibility = Contacts.ID

With that kind of query as the report's record source, the
text box can simply be bound to the FullName field.

Note that a query's Order By clause does not usually
determine the report's sorting. You need to use Sorting and
Grouping to specify the report's sort order.
--
Marsh
MVP [MS Access]

I have made the change to the table as suggested but the reports continue to
return the autonumber rather than the name. What do I need to do to the
controlsource? I have attempted adding the expression below but it doesn't
work.

I have an issues database where I have a field called ‘Responsibility’ that
is set as a Number format and then the following Lookup is set to return the
First & Last Name of the person who is responsible for the issue:

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] AS
Expr1 FROM Contacts ORDER BY Contacts.[Last Name];

This works fine in the forms and when creating the reports I have the
ControlSource set as the ‘Responsibility’ field. However, I know this is
wrong as it returns the Number allocated to the person responsible rather
than their first and last name. Can anyone suggest the correct expression
that I should have in the ControlSource to return the name rather than a
meaningless number?

The text box (and Sorting and Grouping) would just use the
name of the field. In this case it's Expr1. Because that
name provides no clues to its use, you should change the
automatically created name to something more meaningful:

SELECT Contacts.ID, Contacts.[First Name] & " " &
Contacts.[Last Name] AS FullName
FROM Contacts
ORDER BY Contacts.[Last Name]