simple concatenation

[spence]

I have a client table with fields [LastName] and
[FirstName]. It's set up so that if the client is an
agency rather than an individual, the agency name is
entered into field [LastName] and field [FirstName] is
left blank. I need to create a concatenated dropdown for a
form that displays my data:

"LastName, FirstName"(for individuals)
"AgencyName" (for agencies)

I'm using "&" to create the dropdown but I don't know how
to write a statement that will keep my the comma that I
need for individuals from showing up in my Agency records.

Alternatively, if someone thinks it would be smarter to
create a separate field altogether for Agencies and would
like to tell me how to join the fields into a single drop
down that will pull from both, I'm certainly open to
suggestions.

thanks in advance,
spence

 

[Guest]

How about something like this in a query:

IIf (IsNull([FirstName]), [LastName], [LastName] & "," &
[FirstName])

 

[John Vinson]

I need to create a concatenated dropdown for a
form that displays my data:

"LastName, FirstName"(for individuals)
"AgencyName" (for agencies)

One sneaky way to do this is to take advantage of the fact that both
the & operator and the + operator concatenate strings - but & treats
NULL fields as a zero length string, and + "propagates nulls",
returning NULL if either argument is NULL:

[LastName] & (", " + [FirstName])

will include a comma only if FirstName is not NULL.