Viewing names on form.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following on a form field [ SELECT [EmployeeID], [LastName] &
", " & [FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];
] which works ok but we use a room name for some of the things. The room name
is stored in the table under FirstName. How can I get it to show up in the
form without the , if the either the FirstName or the LastName is blank?
Right now it would show [, Roberts] I need it to show up like [ Roberts ].

Thanks
Bobbo
 
Try:
SELECT EmployeeID,
LastName & IIf([LastName] Is Null Or [FirstName] Is Null, Null, ", ") &
FirstName
FROM ...
 
Show us the design of your table like this and we will be better able to
help you:

EmployeeID FirstName LastName RoomNumber

[include 2 or 3 sample records here]

--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei
 
This worked. Thanks for the quick response.

Bobbo

Allen Browne said:
Try:
SELECT EmployeeID,
LastName & IIf([LastName] Is Null Or [FirstName] Is Null, Null, ", ") &
FirstName
FROM ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bobbo said:
I am using the following on a form field [ SELECT [EmployeeID], [LastName]
&
", " & [FirstName] FROM Employees ORDER BY [LastName] & ", " &
[FirstName];
] which works ok but we use a room name for some of the things. The room
name
is stored in the table under FirstName. How can I get it to show up in the
form without the , if the either the FirstName or the LastName is blank?
Right now it would show [, Roberts] I need it to show up like [ Roberts ].
Click to expand...
Click to expand...
 
From what you say you only have to allow for the LastName column being Null,
in which case you can exploit the fact that Nulls propagate, i.e. Null +
anything = Null. So by using the + addition operator rather than the &
concatenation operator and putting the operation in parentheses this will
evaluate to Null if LastName is Null, and consequently suppress the comma,
Also you don't need the full expression in the ORDER BY clause; just the two
columns will do.

SELECT [EmployeeID], ([LastName] + ", ") & [FirstName]
FROM Employees
ORDER BY [LastName], [FirstName];

I would point out that storing the room name as a value in the FirstName
column of the Employees table goes against the principles of the relational
model. In relational-speak any value at any column position in a row in a
table must be a legitimate value of type x, where x is the attribute type
represented by the column, i.e. in this case it should be a legitimate value
of type FirstName, which a room name clearly is not. Its for you to decide
whether to aim for a more orthodox model, of course, but if you wish to
pursue this, post back and I'd be happy to advise further.

Ken Sheridan
Stafford, England
 
Back
Top