Display Null Value in Field B if Field A is Null

[wwconslt]

Hello Everyone,



I thought this query expression would be simple but am having problems.

I have a field called Parent 1 Email. If the [Parent 1 Email] field is null, then I'd like the [EmailType] field to also be Null, else populate with "EH".

I've tried this in design view of the query:

PARENT_EMAILTYPE: IIf([Parent 1 Email] Is Not Null,"EH",Null)

But what's happening is it's populating the email type column in the query with "EH" even if there is no email.

Any help is appreciated.

[aytee111]

IIf(Not IsNull([Parent 1 Email]),"EH")

[aytee111]

Are you sure that Parent 1 Email is null? It may be blank.

IIf(Not IsNull([Parent 1 Email]) And [Parent 1 Email]<>"","EH")

[JoeM]

I have a field called Parent 1 Email. If the [Parent 1 Email] field is null, then I'd like the [EmailType] field to also be Null, else populate with "EH".

This [EmailType] field you are talking about is just a calculated field in a query, right? Not a field in a table?
If you want to update an actual table field, you would need to use an Update Query to do so.

I tested out this expression in a query:

PARENT_EMAILTYPE: IIf([Parent 1 Email] Is Not Null,"EH",Null)

and it seemed to work correctly for me. It only populated "EH" for records where [Parent 1 Email] is not null.
If it is populating all of them, then that field is probably not null. You can confirm that pretty easily with an expression like this:

CHECK: Len([Parent 1 Email])

If it returns any number, then the [Parent 1 Email] field is not null.

[wwconslt]

Thank you everyone for your responses!

aytee111, you were exactly right. The fields must be blank not null so your solution worked! I thought Null and Blank were the same thing. Could you please tell me the difference?

Thanks Joe for the Len tip to check for null values.

[aytee111]

Not sure the exact technical reasons, it is how Access stores the value.