Access 2007: Using if/null expression for two fields

Question

I have two fields A and B and am in need to build an expression to populate Field C. Basically I need it to populate to field A unless field A is null. If field A is Null then field B should populate instead.

In excel this is what my formula would look like:

=IF(ISBLANK(A1),B1,A1)

Now as I try to build an expression in access I am having no such luck. I have used everything from

IIf(IsNull[A],[B],[A])

to:

IIf((IsNull([A])),(IIF(IsNotNull([A])), [A],[B]),[A])

Is there something that I am missing? I hope this simplifed version of my issue makes sense. I appreciate the help.

Answer 1

I don't see a reason to store it in the table. It is derived information that can be calculated on the fly in a query based on the table.

Answer 2

I tried that as well initially but had no such luck. Here is my actual expression with Product Code as Field A and Product Interest (1) as Field B. Fortunately the Nz Expression worked. Thank you for the help.

Adj Prod: IIf((IsNull([Product Code])),(IIF(IsNull([Product Code])), [Product Code],[Product Interest (1)]),[Product Code])

Answer 3

Thank you. A follow up question, however:

Is it safe to store that expression in a field in the table?

Answer 4

Nz should works fine, but instead of IIf(IsNull[A],[B],[A]), you could have tried  iif( IsNull**(** [A] ),  [B], [A] ). You were missing the ( ) for  IsNull.

Answer 5

Try this in a query:

C: Nz([A], [B])