Auto expand not working with comma

[Jeff]

I have a combobox that auto fills the client name as I type, although the
combo box's row source is

SELECT DISTINCTROW Clients.ClientID, [ClientLastName] & ", " &
[ClientFirstName] AS ClientLastandFirst FROM Clients ORDER BY
[ClientLastName] & ", " & [ClientFirstName];

This conveniently shows the whole name first and last. As I start to type
the last name everything works OK but as soon as I type the comma the
expanding (auto fill) stops working. Also if I continue and type the full
name and hit return I get a message that the name is not on the list! Any
Ideas on how to make this work?

 

[Allen Browne]

You RowSource is placing TWO spaces between the last name and first name.
Are you sure you are typing 2 spaces when you try to get a match?

In any case, change the ORDER BY clause to just:
[ClientLastName], [ClientFirstName];
That allows Access to use any indexes on the fields.

 

[Jeff]

Two spaces!.....I feel dumb...simple mistake but I just needed another pair
of eyes to see it. Thank you

You RowSource is placing TWO spaces between the last name and first name.
Are you sure you are typing 2 spaces when you try to get a match?

In any case, change the ORDER BY clause to just:
[ClientLastName], [ClientFirstName];
That allows Access to use any indexes on the fields.

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

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

I have a combobox that auto fills the client name as I type, although the
combo box's row source is

SELECT DISTINCTROW Clients.ClientID, [ClientLastName] & ", " &
[ClientFirstName] AS ClientLastandFirst FROM Clients ORDER BY
[ClientLastName] & ", " & [ClientFirstName];

This conveniently shows the whole name first and last. As I start to type
the last name everything works OK but as soon as I type the comma the
expanding (auto fill) stops working. Also if I continue and type the full
name and hit return I get a message that the name is not on the list! Any
Ideas on how to make this work?