File Name

[Kris D]

I am trying to create a query from a table to display a combo box. If the
first and last name are blank then display the center name, if the center
name is blank then display the first and last name. Here is what I have
displayed in three different fields:

First:
Field: File As: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Center
Name],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " &
[First Name]))
Table: blank

Second:
Field: Provider Name: IIf(IsNull([Last Name]),IIf(IsNull([First
Name]),[Center Name],[First Name]),IIf(IsNull([First Name]),[Last
Name],[First Name] & " " & [Last Name]))
Table: Blank

Third:
Field: Child Care Providers.*
Table: Child Care Providers

When I go to the transactions table I created a combo box and the Auto ID
number displays instead of the first and last name or center name if first
and last name is blank. I am not sure if I have the file name wrong.

 

[Allen Browne]

Perhaps something like this:
IIf([Last Name] Is Null AND [First Name] Is Null, [Center Name],
[Last Name] & ", " + [First Name])

It sounds like you table contains corporate entities (with a Center Name)
and persons (with First Name and Last Name)? If so, you might like to
consider using fields like this:
IsCorporate yes/no True = a centre; False = a person.
MainName Text Center Name or surname (depending on
IsCorporate)
FirstName Text Christian name for persons; contact person
for corporates.

The calculated query field would then be:
MainName & IIf([IsCorporate], ", " + [FirstName], Null)

It's similar to what you have, but makes searches a bit easier, i.e. there's
one main name column to search to find either type of entity.

 

[a a r o n _ k e m p f]

hey; in your other post-- if you want a good books library; I'd just
use the PUBS database.
This is included with SQL Server 2000 by default I think--- or at
least it is in some versions as an option.

Either way-- I think that having a ready made DB is considerably
easier than a schema (that is incomplete)