New to Access 2010, Quick Expression Builder Question 3

[spBrad]

Hi Everyone,

I'm sort of new to Access/databases and I'm using the 2010 version. I've setup multiple tables/reports and defined relationships between them with no issues so far with what I'm doing.

However I wanted to setup a Display Name field using a calculated data type.

In this particular table it has fields for Company, Customer First Name, and Customer Last Name.

If it is an individual customer, only the first and last name fields would be populated with data and the company field would be left blank. If it is a business customer the company field would be populated and the customer first name and last name would be blank.

How do I create an expression for a Display Name field that would pull data from the company field if not blank or if blank combine the first and last name fields?

I know the expression for simply combining first and last name would be:

[First Name] & " " & [Last Name]

But not sure how to do the expression that would define the appropriate behavior based on whether the field is blank.

Any help or advice you could give would be greatly appreciated. If I could just see a correct model of the expression I could use it as a future reference point going forward.

Thanks again,

B R A D

 

[PHV]

I'd try this:
Nz([Company], [First Name] & " " & [Last Name])

Or this:
[Company] & [First Name] + " " + [Last Name])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

IIF(IsNull([Company]),[First Name] & " " & [Last Name],[Company])

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[spBrad]

Thank you so much Missinglinq and PHV for starting my day off great!

IIF(IsNull([Company]),[First Name] & " " & [Last Name],[Company])

Worked like a charm!

Now that I see it laid out like that it totally makes since. The one I had typed in using my flawed logic was twice as long and of course incorrect.

Thanks again, I really appreciate it.

 

[dhookom]

spBrad,
If you wanted to save keystrokes "was twice as long", I typically use either of PH's suggestions. His first expression is the standard while the second is a little "geekier" but is a valuable lesson in when and why to use [red]&[/red] vs [red]+[/red].

Duane
Hook'D on Access
MS Access MVP