How to properly arrange syntax for a "file as" expression?

Question

Hi folks,

I'm currently utilizing the Projects Web Database template in my organization and I would like to rearrange the syntax in the "File As" field of my Customers datasheet so the priority goes to the field for Company instead of Last Name/First Name.  Ideally, the expression would still give a Last Name when Company Name is empty.  See below for the current expression.

IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName]))

The end goal is for a form which references the "File As" field to fetch Company rather than Last Name if there is something in both fields, which should be simple enough but I haven't been able to wrap my head around the above syntax as yet.

Thanks for your time.

Answer 1

Hi,

Try breaking it down into separate pieces and then add each piece together one at a time. For example, since you want the Company name to take precedence, you can check for it first.

IIf(Not IsNull([Company]), [Company],"No Company")

The above will use [Company] if it exists; otherwise, it will use "No Company." If you test it, and it is working as intended, then you can add the LastName instead of "No Company." For example:

IIf(Not IsNull([Company]), [Company], IIf(Not IsNull([LastName]), [LastName], "No Company and no Last Name"))

and so on...

Hope it helps...

Answer 2

Hi youngWMA,

Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

Best Regards,

Terry

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].