Concatenating Fields

  • Thread starter Thread starter Nadihaha
  • Start date Start date
N

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
 
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.
 
Thanks Dave,

Pardon my ignorance but exactly where do I put the > SELECT EmployeeID,
EmployFirstName & " " & EmployeeLastName AS FullName FROM
tblEmployee;?
Click to expand...

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
 
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 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


Nadihaha said:
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 said:
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.
Click to expand...
Click to expand...
 
Thanks sooooooooo much!!!! That works exactly how I pictured it!! near on a
miracle!!

Klatuu said:
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


Nadihaha said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top