If / Then

[Guest]

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
(e-mail address removed)

 

[Jeff Boyce]

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

 

[fredg]

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
(e-mail address removed)

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])

 

[Jeff Boyce]

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.

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
(e-mail address removed)

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])