Lookup list display

[usr33t]

Hi,

I have a database consisting of two forms (MAIN and CONSULTANTS). One of these forms (CONSULTANTS) contains the fields, which are the source for a lookup list of names on the first form (MAIN). The fields contained in the CONSULTANTS form/table are as follows;

TITLE (Mr, Mrs, Dr. etc)
FIRSTINITIAL
SECONDINITIAL
SURNAME

The row source for the lookuplist field on the first form (MAIN) is;

SELECT consultants.Surname & ", " & consultants.title & " " & consultants.Initial1 & ". " & consultants.initial2 & "." FROM consultants ORDER BY consultants.Surname

This displays the name of a doctor in the following format;

HARRIS, DR. R. J.

This works just fine....

However, in some instances individuals only have one initial and when this occurs the name of a doctor is displayed as follows;

HARRIS, Dr. R. .

I have no problem understanding why this occurs, however, I would be grateful if anyone could suggest how (i.e. what to type and where) to prevent the second "dot" from appearing in instances where individuals only have one initial. Presumably I have to adapt the SQL statement to ignore instances where the SECONDINITIAL field is null. However, I don't know how or where to do this. If anyone can help I'd be most grateful.

If you need any more info then let me know.

Many thanks in advance for your help.

Kind regards

Russell
Usr33t

 

[KenHigg]

SELECT consultants.Surname & ", " & consultants.title & " " & consultants.Initial1 & ". " & consultants.initial2 & iif(isnull(consultants.initial2),"", ".") FROM consultants ORDER BY consultants.Surname

I'm not 100% sure on this one but it's worth a try...

kh

 

[usr33t]

Lookup list of names

Ken,

Cheers, that's brilliant!

You may not have been 100% sure, but you were 100% right!

Thanks a lot for that.

Best regards
Russell
Usr33t

 

[KenHigg]

Cool - Glad to help!