Concatenate help needed

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Hi,
I use a combo box on a form so that users can select names
of students. I altered the undelying row source query to
concatenate two fields so that it appears as "LastName,
FirstName". That works fine. However, there are several
students who have the same name, so I thought the best way
to signify to users which student is which, would be to
add thier student ID after their name. What I'd really
like to do is have the ID appear only if a duplicate name
exists in the recordset. If the name is unique,
just "lastname, firstname" appears. If
duplicate, "LastName, FirstName (Student ID)" appears.
Thanks for any help on how to do this,
-RIck
 
Try this in the query as the calculated field (note: if you have a lot of
records, it will run a bit slowly):

FullName: [LastName] & ", " & [FirstName] & IIf(DCount("*", "TableName",
"[LastName]='" & [LastName] & "' And [FirstName]='" & [FirstName] & "'") >
0, " (" & [StudentID] & ")", "")
 
Ken,

Shouldn't that be > 1 vice greater than 0 in the DCount? I would think that you
would always get one match with this code.



Ken said:
Try this in the query as the calculated field (note: if you have a lot of
records, it will run a bit slowly):

FullName: [LastName] & ", " & [FirstName] & IIf(DCount("*", "TableName",
"[LastName]='" & [LastName] & "' And [FirstName]='" & [FirstName] & "'") >
0, " (" & [StudentID] & ")", "")

--
Ken Snell
<MS ACCESS MVP>

Rick said:
Hi,
I use a combo box on a form so that users can select names
of students. I altered the undelying row source query to
concatenate two fields so that it appears as "LastName,
FirstName". That works fine. However, there are several
students who have the same name, so I thought the best way
to signify to users which student is which, would be to
add thier student ID after their name. What I'd really
like to do is have the ID appear only if a duplicate name
exists in the recordset. If the name is unique,
just "lastname, firstname" appears. If
duplicate, "LastName, FirstName (Student ID)" appears.
Thanks for any help on how to do this,
-RIck
Click to expand...
Click to expand...
 
Thanks to both for the help. I think it is 1 not 0 that
is needed to make it work properly. However, my machine
ground to a halt after making the change so it is not a
practical solution. I'll just use two columns in my
pulldown, one with name, one with Student ID. It will
work fine. Thanks!
-Rick
 
John Spencer (MVP) said:
Ken,

Shouldn't that be > 1 vice greater than 0 in the DCount? I would think that you
would always get one match with this code.
Click to expand...

Yep! Thanks, John!
 
Back
Top