Link to home
Create AccountLog in
Avatar of Michael Dean
Michael DeanFlag for United States of America

asked on

Combo Box

I have a combo box  with the follow:

SELECT Strings.Claimant
FROM Strings
GROUP BY Strings.Claimant
ORDER BY Strings.Claimant;

The problem is we want to the select the Claimant but their last name , however Claimant is a single column with firstname and last name.  I would rather not have to change the tables design to have the first name and last name seperate, is there a way to do this?
SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Generally you can't do it, because first and last name can have more then one word. How can you do it with names like:
Wernher Magnus Maximilian von Braun
John Fitzgerald Kennedy
Jacqueline Kennedy Onassis
seamus9909,

My suggestion is of course assuming very well behaved data in the form "First Last"  without secondary "words" in either part of the name.

IF your data contains exceptions to this rule, then you really should redesign your table (which is the all-around 'best solution' anyhow).
Avatar of Michael Dean

ASKER

The data doesnt contain an exceptions.   When i try to run that SQL I receive a data mismatch error??
Do you have any blank/null or one-name clamaints (no last name)?


Try this:

SELECT Strings.Claimant, Mid(Claimant, instr(1, Claimant," ") + 1)
FROM Strings
WHERE Claimant & "" <> ""
GROUP BY Strings.Claimant
ORDER BY Mid(Claimant, instr(1, Claimant," ") + 1)

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
so that works.  So that combo box (combo171) is used as the Master for a subform.

The subform uses the Master Field as combo171
and the child as "claimant".  That way if you select in combo box a name that has multiple records for the Claimant they will display in the subform.

So im thinking I have to change the Parent and Child relationship in the form correct?

Like    Master is  combo171.column(2)
 since the claimant is in COlumn2 of the SQl for the Combobox?