Calculated form field (1 Viewer)

[jgneely]

I am just a beginner in Access, so please be patient with me. Can anyone help with this situation? I've created a simple address database. I put a text field onto my form that will concatenate the first and last names of the contact for each record so that it is noticeable to all that are perusing the database. My problem is that I have a lot of records that do not have any contact names but names of businesses. So when you come to a field that doesn't have a contact name, my calculated field is blank. I'm not good with calculations or expressions, so what formula should I enter, if, let's say, the [LASTNAME] field is blank, to then enter the [ORGANIZATION] name? Anyone willing to help me out on this one?

Thanking you in advance!

JN

 

[KenHigg]

Look at the iif() function (Referred to as 'Immediate if').

 

[jgneely]

Look at the iif() function (Referred to as 'Immediate if').

I'm sorry, but your solution is greek to me. How should I set up the IIF function to both concatenoate the first and last name fields and if they are null, to then use the organization field in this unbound field?

 

[jdwhytie]

All you have to do is open your access help file and look for the IIf Function:

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

Remarks

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

 

[jgneely]

All you have to do is open your access help file and look for the IIf Function:

I'm still in the dark. I found this example and tried using it and it is doing part of what I want it to do.

Here it is:

=IIf(IsNull(lastname])" ",[firstname]&" "&[lastname]&", "&[CompanyName])

This is what I get:

Jane Doe, XYZ, Company.

What I want is to show first and last name only if there is something in those fields, but if there is no first and last names and only company, then I want company to display.

Now, how do I re-write the above expression.

Please be reminded, that I am a beginner and this is the first time I've ever used, let alone, seen an "IIF" expression.

Help, I'm frustrated. I'm almost done with this database and need to get it up and running for others to use.

Thanks!

JN

 

[Rich]

Wrong way around
=IIf(IsNull(lastname]),[CompanyName],[firstname]&" "&[lastname] )

 

[jgneely]

Wrong way around
=IIf(IsNull(lastname]),[CompanyName],[firstname]&" "&[lastname] )

I got this:

#Name?

What happened and what is that?

 

[jgneely]

I got this:

#Name?

What happened and what is that?

I'm sorry, I had a missing parenthesis.

It works now.

Thanks a bunch