Empty field in table 1

[aas1611]

Hi,

I want to display a name, that is divided by three fields:
Title (Prof., Dr.)
Firstname
Lastname

Unfortunately, some data don't have title and/or firstname (they all have lastname). So, the logic is:
Titel Firstname
----- ---------
if 0 0 -> then displays only lastname
if 0 1 -> then displays firstname + ' ' + lastname
if 1 0 -> then displays title + ' ' + lastname
if 1 1 -> then displays titel + ' ' + firstname + ' ' + lastname

0 represents empty fields, 1 represents non-empty fields. I hope this table is understandable.

I have written a code like this in the design query:

Name: When([archiv unternehmen].[title]=''; When([archiv unternehmen].[firstname]='';[archiv unternehmen].[lastname];[archiv unternehmen].[firstname] & ' ' & [archiv unternehmen].[lastname]);When([archiv unternehmen].[firstname]='';[archiv unternehmen].[title] & ' ' & [archiv unternehmen].[lastname];[archiv unternehmen].[title] & ' ' & [archiv unternehmen].[firstname] & ' ' & [archiv unternehmen].[lastname]))

(The table is called "archiv unternehmen". It's German.)
So, it's nested if. But I don't get what I want. Some came up something like JohnDoe, or 1-2 spaces at front. Can somebody check if my code is correct? Or maybe have a better approach?

Another question, is it possible that an access table contains whitespaces, so it looks empty to human eyes but actually not?

Thanks for any help.
Andre

 

[lynchg]

Try:

Name: IIf(Len(Trim([archiv unternehmen].[title]))= 0, IIf(Len(Trim([archiv unternehmen].[firstname]))= 0,[archiv unternehmen].[lastname], [archiv unternehmen].[firstname] & ' ' & [archiv unternehmen].[lastname]), [archiv unternehmen].[title] & ' ' & [archiv unternehmen].[firstname] & ' ' & [archiv unternehmen].[lastname])

 

[jedraw]

The code provided by lynchg will do what you want. However, if there is no title, it will give a leading space in the "Name".

If that is a problem, then use this slightly modified version:

Name: Trim(IIf(Len(Trim([archiv unternehmen].[title]))=0,IIf(Len(Trim([archiv unternehmen].[firstname]))=0,[archiv unternehmen].[lastname],[archiv unternehmen].[firstname] & ' ' & [archiv unternehmen].[lastname]),[archiv unternehmen].[title] & ' ' & [archiv unternehmen].[firstname] & ' ' & [archiv unternehmen].[lastname]))

 

[PHV]

Another way:
Name: IIf(Trim([archiv unternehmen].[title])='';Null;Trim([archiv unternehmen].[title]))+' ' & IIf(Trim([archiv unternehmen].[firstname])='';Null;Trim([archiv unternehmen].[firstname]))+' ' & Trim([archiv unternehmen].[lastname])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[aas1611]

Thanks for all your hints!

Andre