"Natural" name format in combo list

[davidbodhi]

Hi - I'm very new and totally self-taught = ignorant. Sorry.

I have a table called Contacts that includes the following name fields:

Prefix
First_Name
MI
Last_Name
Suffix

There is also a form called Contacts, where a user can input data on new people. On this form is a field called Full Name that automatically takes the info filled into the above fields and generates a "natural name format" so that the user enters:

Prefix: Dr.
First_Name: Long
MI: J.
Last_Name: Silver
Suffix: phD

and Full Name displays "Dr. Long J. Silver, phD"

To handle situations where some fields may not have data I use the following code of nested IIf's:

IIf(IsNull[Prefix],
IIF(IsNull[MI],
IIf(IsNull[Suffix],
[First_Name]+" "+[Last_Name],
[First_Name]+" "+[Last_Name]+" "+[Suffix]),
IIf(IsNull[Suffix],
[First_Name]+" "+[MI]+" "+[Last_Name],
[First_Name]+" "+[MI]+" "+[Last_Name]+" "+[Suffix]))
,IIF(IsNull[MI],
IIf(IsNull[Suffix],
[Prefix]+" "+[First_Name]+" "+[Last_Name],
[Prefix]+" "+[First_Name]+" "+[Last_Name]+" "+[Suffix]),
IIf(IsNull[Suffix],
[Prefix]+" "+[First_Name]+" "+[MI]+" "+[Last_Name],
[Prefix]+" "+[First_Name]+" "+[MI]+" "+[Last_Name]+" "+[Suffix])))

PROBLEM:
I also have a form called Collected at Time of Visit. On it is a combo box where the user can select a returning Patient, from the table Contacts, to record a new financial transaction. The combo box is working fine, but I want to FORMAT the appearance of the names so they look like the above "Dr. Long J. Silver, phD", rather than showing the columns, as is usual.

I have tried variations on the above code, but get errors, either that I'm referring to fields that don't exist or to objects that don't exist, even when I use the wizard to navigate to the correct fields in the correct table.

Can anyone help me get this to work?

I might as well also mention, the form Contacts also has a pick list Contact_Type that includes Patient, Doctor, Organization, etc as options. I'd like to combo box in question to FILTER so it only displays Patients.

Help with that, too? I'll be very appreciative!

Thanks in advance!

 

[ghudson]

In the SQL for your combo box you need to create a new field [column] and concatenate [merge] the fields. You should be able to use something like what you already have and only display the new concatenated field in the combo box, leave the rest of the fields hidden [set their column width to 0].

 

[davidbodhi]

"Natural name format in combo list

Thanks for the hint.

I find that if I use the following SQL statement, I get every record listed, but only first and last name fields, of course. Since some contacts require more than those two fields, I can't leave it like that:

SELECT Contacts.Prefix, Contacts.First_Name, Contacts.MI, Contacts.Last_Name, Contacts.Suffix,Contacts.First_Name+" "+Contacts.Last_Name AS Patient_Name
FROM Contacts;


If I use this one, I get blank lines wherever the Prefix field is empty:

SELECT Contacts.Prefix, Contacts.First_Name, Contacts.MI, Contacts.Last_Name, Contacts.Suffix,Contacts.Prefix+" "+Contacts.First_Name+" "+Contacts.Last_Name AS Patient_Name
FROM Contacts;

If I use the IIf function to handle instances when the Prefix field is empty, as follows, I get a box popping up asking the user to enter the IsNullContacts.Prefix parameter when I click on the list:

SELECT Contacts.Prefix, Contacts.First_Name, Contacts.MI, Contacts.Last_Name, Contacts.Suffix, IIf(IsNullContacts.Prefix,"",Contacts.Prefix+" ")+Contacts.First_Name+" "+Contacts.Last_Name AS Patient_Name
FROM Contacts;

I'm afraid I don't know why it's asking for a parameter. I tried IsEmpty instead of IsNull and got the same thing. It seems like the IIf isn't recognizing the field.

Can you help me with this issue?

 

[davidbodhi]

Never Mind.... I figured it out.

The SQL version of IIf needed the syntax IIf(IsNull([FIELDNAME]),

when the 'pure Access' version worked with IIf(IsNullFIELDNAME,

Go figure..........