IIF question 2

[tripleblessed]

I'm trying to create some address labels. I would like to show the Family Name (i.e. The Smith Family) if not null else I'd like to show Title & Last Name.

On my report design, this is what I have:
=IIf((Trim([Xmas])="xmas" And Trim([Family Name])<>""),UCase([Family Name]),UCase([Title)] & “ “ & UCase([Last Name]))

But I'm getting a syntax error. Thanks for helping this beginner get her Christmas labels done!

 

[Shirley]

=IIf(Trim([Xmas])="xmas" And Trim([Family Name])<>"",UCase([Family Name]),UCase([Title)] & “ “ & UCase([Last Name]))

Try this and see if this works.

 

[tripleblessed]

Hi Shirley,
Thanks for your quick response. It still gives me an invalid syntax error. I also just realized that I do not need the ="xmas" part since my query is selecting these fields so I removed that part to show:

=IIf((Trim([Family Name])<>""),UCase([Family Name]),UCase([Title)] & “ “ & UCase([Last Name]))

This also gives me an invalid syntax error - something about "You may have entered an operand without an operator.

 

[Shirley]

=IIf([Family Name]<>"",(UCase([Family Name])),(UCase([Title)] & “ “ & UCase([Last Name])))

Try this Simple one first and then add the Trims and UCase

= IIf([FamilyName]="", [FamilyName], ([Title]&" " &[LastName]))

 

[dhookom]

Don't make the mistake of thinking "" is the same a null. If there is no value in a field, it isn't the same as "".

You expression looks like you reversed a ]) after Title

=IIf((Trim([Family Name])<>""),UCase([Family Name]),UCase([Title]) & “ “ & UCase([Last Name]))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tripleblessed]

Thank you for both of your replies. Shirley -I used the basic expression you suggested and it worked great. dhookom - I understand now about the "" is not the same as null. I guess I assumed that the blank fields were Null.

I was only using Trim and UCase b/c I copied it from old code. I understand that the UCase is for Uppercase but I don't understand the Trim - unless it just cuts off extra blank spaces at the end of a field?

 

[Shirley]

Trim deletes any space in front of and behind the field. There is RTrim which will delete the space in the back of the field, and LTrim for the front of the field.

 

[sfatz]

You may still have cases where the simple doesn't work as expected. I've seen in other posting where people concatenated "" to the field to avoid null problems.

Your simple query would then look like this:
= IIf((""&[FamilyName])="", [FamilyName], ([Title]&" " &[LastName]))

just my 2¢