Help with IIF statement

[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

 

[Ken Snell \(MVP\)]

How about a simple expression that doesn't need IIf at all:

Salutation: [firstNM] & (" and " + [first2])

 

[Van T. Dinh]

Try:

salutation: [firstNM] & (" and " + [first1])

 

[robert]

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?

 

[Van T. Dinh]

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]), "")

 

[robert]

Sorry for the delay, but LEN function works perfect many, many thanks
for that!

 

[robert]

Sorry for the delay, but LEN function works perfect many, many thanks
for that!

 

[kpdorset]

First time I've ever tried to answer a question on a forum so I'll
probably be wrong but your problem intrigued me so I practiced and got
IIf (IsNull[first2] , [firstNM], [firstNM] & " and " &
[first1]) to work

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 a required: no, allow zero length: yes