Concatenate help needed

[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

 

[Ken Snell]

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] & ")", "")

 

[John Spencer (MVP)]

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.

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>

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

 

[Rick]

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

 

[Ken Snell]

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.

Yep! Thanks, John!