Multi Select List Box formatting

[retro]

I am using a Multi Select List Box to display a list of names from a table, in order to select e-mail recipients. The names are in two fields: [Main]![First Name] and [Main]![Surname].

I have done this by using the following code for the rowsource:

SELECT DISTINCTROW Main![e-mail], Main![First Name], Main!Surname FROM Main;

This gives a rather undesired effect, in that shorter first names have a large gap before the surname. In addition, there is a dull separating line between the two columns, thus:

Jane        [COLOR="Silver"]|[/COLOR] Smith
Bartholomew [COLOR="silver"]|[/COLOR] Simpson

My questions:

1. Is it possible to arrange the names so they appear in one column, with a space between them? (i.e. they would appear as written)
2. If not, can I get rid of the separating line?
   
   Many thanks in advance if you can help!

 

[ssteinke]

1. Is it possible to arrange the names so they appear in one column, with a space between them? (i.e. they would appear as written)

Yes

Try this in your query

SELECT DISTINCTROW Main![e-mail], Main![First Name] & " " & Main!Surname AS [FullName] FROM Main;

You will notice that by removing the comma from the second column in your query, and simply using a space seperator, you should get the desired effect. I also used the alias [FullName] to identify your combined field. Don't forget that you will need to reduce the number of columns in your listbox to reflect the new column count.

Good luck,

Scott

 

[ssteinke]

What i meant to say was, ditto, what Pat said

 

[retro]

That's lovely guys - worked a treat!

Many thanks