M
MCB
I think DLookup might be what I need for this report field, but I've never
used it, and the formatting is confusing me.
I have a report that displays the name of a team lead and all the existing
cases assigned to him/her. Team Lead information is stored on tblTeamLeads
(TL ID, First Name, and Last Name). On that report I have a field for "Team
Lead" and a field for "Completed By", which displays which team lead
completed each displayed case. The team lead who completed the case is not
always the same team lead to whom the case is assigned, but no one other than
team leads can have a case assigned or complete a case. "Completed By" is
populated by the user, who selects a team lead's name from a combo box on
frmCase (it's then stored in the field "Completed By" in tblCase).
The problem is that the "Completed By" field is only displaying the team
lead's first name on the report, and I want it to display first and last name
(since some team leads have the same first name). I guess this is because
when users originally select a name to complete the "Completed By" field,
only the first name is displayed in that field, so I guess that's how it's
stored.
I created qryTeamLeads as the source for this report (rptTeamLeads), but
can't get the combination of First and Last Names to appear correctly for the
"Completed By" field. I tried creating a second query (qryCompletedBy), which
works on its own - it displays the stored First Name of the team leader who
completed the case, and thanks to a relationship between "Completed By" and
"TeamLead ID" on the tblTeamLeads form, it displays the Last Name as well.
However, I can't get it to work in the report - I get an error about multiple
instances of "Last Name" (I use the "First Name" and "Last Name" fields from
tblTeamLeads already to display the name of the team lead to whom the cases
are assigned).
How can I fix this and get both first and last names to appear for
"Completed By"? (And not the first and last names of the team lead to whom
the case is assigned, rather than the team lead who completed it, which is
another problem I've had.) Is DLookup the right function for this? If so,
what's the syntax?
Sorry for being so long-winded, though I hope that's clear.
used it, and the formatting is confusing me.
I have a report that displays the name of a team lead and all the existing
cases assigned to him/her. Team Lead information is stored on tblTeamLeads
(TL ID, First Name, and Last Name). On that report I have a field for "Team
Lead" and a field for "Completed By", which displays which team lead
completed each displayed case. The team lead who completed the case is not
always the same team lead to whom the case is assigned, but no one other than
team leads can have a case assigned or complete a case. "Completed By" is
populated by the user, who selects a team lead's name from a combo box on
frmCase (it's then stored in the field "Completed By" in tblCase).
The problem is that the "Completed By" field is only displaying the team
lead's first name on the report, and I want it to display first and last name
(since some team leads have the same first name). I guess this is because
when users originally select a name to complete the "Completed By" field,
only the first name is displayed in that field, so I guess that's how it's
stored.
I created qryTeamLeads as the source for this report (rptTeamLeads), but
can't get the combination of First and Last Names to appear correctly for the
"Completed By" field. I tried creating a second query (qryCompletedBy), which
works on its own - it displays the stored First Name of the team leader who
completed the case, and thanks to a relationship between "Completed By" and
"TeamLead ID" on the tblTeamLeads form, it displays the Last Name as well.
However, I can't get it to work in the report - I get an error about multiple
instances of "Last Name" (I use the "First Name" and "Last Name" fields from
tblTeamLeads already to display the name of the team lead to whom the cases
are assigned).
How can I fix this and get both first and last names to appear for
"Completed By"? (And not the first and last names of the team lead to whom
the case is assigned, rather than the team lead who completed it, which is
another problem I've had.) Is DLookup the right function for this? If so,
what's the syntax?
Sorry for being so long-winded, though I hope that's clear.