If / Then

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tgwarrior - 27 Jul 2006 23:53 GMT

I have a fairly simple if/then question I cannot figure out.

I'd like to have a query that places [Firstname]+[Lastname] fields into a
new field if data exists in [LastName]. If [LastName] is null, I'd like the
field to fill in [OrganizationName]

What I've tried is:

Expr1: IIf([LastName]="is not null",[FirstName]+[LastName],[Organization],)

What happens? For those records that do have a last name but no
organization, I get NOTHING in the Expr1 field. I do get the Organization
name to fill in, when the organization field is not null, regardless of if
there is a last name or not.

I'm NOT getting what I want, which is to have the [FirstName]+[LastName]
fields be placed first in the Expr if there is data in those fields. If
there is no data in the [LastName] field, I'd like data from the
[Organization] field to be put in the expression.

What am I doing wrong?

Thanks

michael munson
tgwarrior@forge-forward.org

Reply to this Message

Jeff Boyce - 28 Jul 2006 00:44 GMT

You are asking Access to check the [LastName] field to see if it matches the
string "is not null". I'll take a wild guess that no one in your database
has that for a last name <g>.

Another approach would be:

IIF(Nz([LastName],"")<>"",[FirstName] & " " & [LastName],[Organization])

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

>I have a fairly simple if/then question I cannot figure out.
>
[quoted text clipped - 24 lines]
> michael munson
> tgwarrior@forge-forward.org

Reply to this Message

fredg - 28 Jul 2006 01:01 GMT

> I have a fairly simple if/then question I cannot figure out.
>
[quoted text clipped - 22 lines]
> michael munson
> tgwarrior@forge-forward.org

You have your criteria looking for a [LastName] that literally equals
"is not null", when what you rally meant was is not null (no quotes).

The & is the concatanation character, not the + (though you can use +
if you are aware of the potential problems), and you have an extra
comma towards the end of the expression. Also you did not indicate a
space between the First and Last names, i.e. FrankSmith (unless that
is what you actually want).

Try it this way:

Expr1:IIf([LastName] is not null,[FirstName] & " " &
[LastName],[Organization])

However, I prefer IsNull() instead:
Expr1: IIf(IsNull([LastName]),[Organization],[FirstName] & " " &
[LastName])

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Jeff Boyce - 28 Jul 2006 15:14 GMT

Fred

I agree with how you are testing for Null.

I've had the misfortune to run into several circumstances where the "value"
that was stored was erased, resulting in a "zero-length string" (i.e., the
""). Testing for Null in those instances does not find the zls, hence, my
use of Nz(xxxxx, "") <> "".

Of course, this is obviated if the field does not allow zls (which Access
DOES allow, by default!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

>> I have a fairly simple if/then question I cannot figure out.
>>
[quoted text clipped - 44 lines]
> Expr1: IIf(IsNull([LastName]),[Organization],[FirstName] & " " &
> [LastName])

Reply to this Message