Software

Ask something
Question Locked

Concatenating only some entries, not all.

0 helpful
4 replies

by barbra

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello,

I have a table of all my companies employees by last name, first name, and emp#. There is also an entry to the list called QA Manager, with the two words split between the first and last name fields in the table.

I have several forms that require an employee's name to be entered and in a concatenated field with a comma separating the first and last names. i do not want Access 2007 to concatenate the QA Manager though. I want it to display without the comma if possible.

It isn't a huge deal, but it would be nicer that way. Is there a way to get access to ignore that particular entry in my expression?

4 replies

by barbra

No, the concatenation rule would not apply to an entry such as first name "QA" last name "Manager" but it would apply to an entry such as first name "John" last name "Smith".

I can turn off the requirement that both first and last name be entered and move the entire word "QA Manager" to one field or the other, although I would prefer not to do that. I do not know the syntax to use to construct the filter if I did use that method though.

by tony hopkinson

'hard coding' I'm afraid

Select [LastName] + ', ' + [FirstName] From Employees where ([LastName] <> 'Manager' and [FirstName] <> 'QA')
UNION
Select [LastName] + ' ' + [FirstName] From Employees where ([LastName] = 'Manager' and [FirstName] = 'QA')

For instance.

Personally I'd be looking at storing QA Manager in another table (not an employee is it!)
or adding a flag column to employees.
or addung a correspondancename column and prefilling it on entry.

Sound's wasteful but gives you a way of taking out the name format from the scope, say if you wanted to deal with
Mr, Miss, Ms. Sir.... at some point.

This discussion is closed to new replies.