Concatenating Fields

[Nadihaha]

Ok We're using Office 2003. I'm sure this answer is probably in here
somewhere but I can't find it!!

I'm creating a HR Database - What I want it to is select a Person from a
Combo Box (I have that much) and store they're employee ID in the appropriate
field - I have that bit down pat. However then in the combo box it only shows
the first name, I would like it to show both first and surname. Is there an
easy way to do this?

I suspect I'll have to have a query where it's looked up from where I can
concatenate the First Name and Last name so that when the Combo box on my
form looks it up it's in the one field but I can't figure out how to
concatenate the 2 fields.

I hope I make sense!! Any help is greatly appreciated. THanks

 

[Klatuu]

Yes, it is a query you need. You need to use a query as the row source of
your combo. You can combine the name using something like this:
SELECT EmployeeID, EmployFirstName & " " & EmployeeLastName AS FullName FROM
tblEmployee;

Note that you should check your combo's column count property because it
sounds like you will have one less column than you have now.

 

[Nadihaha]

Thanks Dave,

Pardon my ignorance but exactly where do I put the > SELECT EmployeeID,
EmployFirstName & " " & EmployeeLastName AS FullName FROM

tblEmployee;?

Also what do you mean by Check My Column Count? Do you mean that if I
increase my Column Count the Surname Column should appear?

I'm kinda new to making my own access databases and getting them to do what
I want.

Thanks

 

[Nadihaha]

Ok After looking at it again I think I get what you mean but I'm going to
check anyway.

Currently I have the Row Source on my Combo Box as SELECT [Employee
Query].[Employee Name], [Employee Query].[Employee Surname], [Employee
Query].[Employee Number] FROM [Employee Query] ORDER BY [Employee Surname],
[Employee Name], [Employee Number];

Are you saying I should replace that with what you suggested?

Thanks Heaps!!

 

[Klatuu]

Yes, it is the combo row source. I made the change to it. It should be:

SELECT [Employee
Query].[Employee Name] & " " & [Employee Query].[Employee Surname] AS
FullName, [Employee
Query].[Employee Number] FROM [Employee Query] ORDER BY [Employee Surname],
[Employee Name], [Employee Number];

As to the column count, what you have now is 3 columns (Name, Surname, and
Number). Since you are concatenating the 2 name columns, you now have only 2
(FullName, and Number)
--
Dave Hargis, Microsoft Access MVP

Ok After looking at it again I think I get what you mean but I'm going to
check anyway.

Currently I have the Row Source on my Combo Box as SELECT [Employee
Query].[Employee Name], [Employee Query].[Employee Surname], [Employee
Query].[Employee Number] FROM [Employee Query] ORDER BY [Employee Surname],
[Employee Name], [Employee Number];

Are you saying I should replace that with what you suggested?

Thanks Heaps!!

Yes, it is a query you need. You need to use a query as the row source of
your combo. You can combine the name using something like this:
SELECT EmployeeID, EmployFirstName & " " & EmployeeLastName AS FullName FROM
tblEmployee;

Note that you should check your combo's column count property because it
sounds like you will have one less column than you have now.

 

[Nadihaha]

Thanks sooooooooo much!!!! That works exactly how I pictured it!! near on a
miracle!!

Yes, it is the combo row source. I made the change to it. It should be:

SELECT [Employee
Query].[Employee Name] & " " & [Employee Query].[Employee Surname] AS
FullName, [Employee
Query].[Employee Number] FROM [Employee Query] ORDER BY [Employee Surname],
[Employee Name], [Employee Number];

As to the column count, what you have now is 3 columns (Name, Surname, and
Number). Since you are concatenating the 2 name columns, you now have only 2
(FullName, and Number)
--
Dave Hargis, Microsoft Access MVP

Ok After looking at it again I think I get what you mean but I'm going to
check anyway.

Currently I have the Row Source on my Combo Box as SELECT [Employee
Query].[Employee Name], [Employee Query].[Employee Surname], [Employee
Query].[Employee Number] FROM [Employee Query] ORDER BY [Employee Surname],
[Employee Name], [Employee Number];

Are you saying I should replace that with what you suggested?

Thanks Heaps!!

Yes, it is a query you need. You need to use a query as the row source of
your combo. You can combine the name using something like this:
SELECT EmployeeID, EmployFirstName & " " & EmployeeLastName AS FullName FROM
tblEmployee;

Note that you should check your combo's column count property because it
sounds like you will have one less column than you have now.
--
Dave Hargis, Microsoft Access MVP


:

Ok We're using Office 2003. I'm sure this answer is probably in here
somewhere but I can't find it!!

I'm creating a HR Database - What I want it to is select a Person from a
Combo Box (I have that much) and store they're employee ID in the appropriate
field - I have that bit down pat. However then in the combo box it only shows
the first name, I would like it to show both first and surname. Is there an
easy way to do this?

I suspect I'll have to have a query where it's looked up from where I can
concatenate the First Name and Last name so that when the Combo box on my
form looks it up it's in the one field but I can't figure out how to
concatenate the 2 fields.

I hope I make sense!! Any help is greatly appreciated. THanks