surname not being pick up in combo box

[Chimp8471]

i have a combo box that picks up a distinct row from my table

when i click on the combo box it displays

personnel number
Forname

these work fine, however i now want it to also add the surname to the viewable data.

i have the following in the row source:

SELECT DISTINCTROW [Personnel Data].[Personnel_Number], [Personnel Data].[Forename], [Personnel Data].[Surname] FROM [Personnel Data];

but if i remove the [Personnel Data].[Surname] part it works fine, but obviously only displays relevant data

any ideas

cheers

Andy

 

[Mark Wild]

Andy,

In the properties - format - column count of the combo box check how many columns there are. You want the number to be 3.

For column widths, try 2.54;2.54;2.54

HTH

 

[Chimp8471]

many thanks that worked,

while we are on the subject, i currently display in a label the first name as a result of the combo.

this is as follows =[scrstudent].[column][1]

how would i then also pick up the surname

which is column 2

 

[Mile-O]

Think about it...

If forename is in Column 1 then the surname is column 2...

...that means...

=[scrstudent].[column][2]

 

[Chimp8471]

sorry didnt explain my self to well i need both columns 1 & 2

 

[Mile-O]

Change your SQL to:

SELECT DISTINCTROW [Personnel Data].[Personnel_Number], [Personnel Data].[Surname] & ", " & [Personnel Data].[Forename] As FullName FROM [Personnel Data];

Set the columns count to 2 and this will list your full names as one column in your combobox.

The format in the SQL above will return a Smith, John format but you can easily tweak that to what you want.

 

[Chimp8471]

once again thanks