Help with IIF statement

  • Thread starter Thread starter robert
  • Start date Start date
R

robert

I may have the wrong expression but here is my situation:

I have 4 database fields:
firstNM - users FIRST NAME
lastNM - users LAST NAME
first1 - users spouses first name
first2 - users spouses last name

not all entrys have spouses, but my database is large and I'm trying to
build a 'salutation' expression that merges these fields based on
criteria, I think I need to us IIF but I havent gotten it right yet,
here is my current code:

salutation: iif ([first2] IS NULL, [firstNM], [firstNM] & " and " &
[first1])


translated: if the user does not list a spouse the 'salutation' column
should ONLY list the first name or 'Joe", but if the user does have a
spouse, the 'salutation' column should list the first name of both
entrys, or "Joe and Mary". ...

If there is a spouse, it works fine, i get 'Joe and Mary', but if there
is no spouse I get this 'Joe and' where am I going wrong?

all 4 columns are: required: no, allow zero length: yes
 
How about a simple expression that doesn't need IIf at all:

Salutation: [firstNM] & (" and " + [first2])
 
That is still resulting in "Gary and " if the first1 or first2 is
empty. do I need to add some type of empty string expression?
 
Re-reading your question, I think you need to consider the case where
[first2] has an entry but [first1] is empty since this will give you " and +
(nothing)" with your logic.

It is also possible that your Fields have empty String value rather than
Null.

You may like to try:

Salutation: [firstNM] & IIf( Len(Trim([first1])) > 0, " and " &
Trim([first1]), "")
 
Back
Top