CODEWITHSUNDEEP
ms-access
user2315587
user2315587

Reputation: 21

IIf function in Access; establishing hierarchy

I've found a couple answers that are similar to my situation, but I can't seem to adapt them.

I have a form in which people can enter either a Ship To or a Sold To number (two different columns) and then notes. Only a select few of either type will have notes but all records will need to return as part of a larger query. I want to establish a hierarchy with respect to the notes. Basically:

  1. If the Sold to is not null and the Ship to is null, return the Sold to notes.
  2. If the Ship to is not null and the Sold to is null, return the Ship to notes.
  3. If the Ship to is not null and the Sold to is not null, return the Ship to notes.
  4. If both are null, do nothing (as in return the field blank)

Is that sufficiently clear? I'm pretty sure I should use the IIf function for this, but I'm not sure beyond that.

Upvotes: 2

Views: 88

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123819

IIf() would do it, but Switch() would be a bit more compact. Try something like this:

Switch(Not IsNull([Ship to]), [Ship to notes], Not IsNull([Sold to]), [Sold to notes])

For more information on Switch(), look here.

Upvotes: 1

Related Questions