IIF Function

R

Roger Bell

I have the following in a text box ia Report which is returning an Error:
=Trim("Dear" & " " & IIf([pref]>0,[pref],IIf(InStr([firstname],"
")>0,Left([Firstname],InStr([Firstname]," ")-1),[firstname])))
What i am attempting is:
If the field [Pref] is greater than 0, then display [Pref], otherwise If the
field [firstname] is greater than 0, display just the first part of this
name, otherwise display the firstname.
The after [pref], works fine, so the problem appears to be in the code
preceeding this.

Any help would be appreciated.
 
D

Douglas J. Steele

Assuming that pref and firstname are text fields, try using Len([pref]) and
Len([firstname]), or, to handle the case where they might be Null,
Len([pref] & "") and Len([firstname] & "")
 
K

KARL DEWEY

Your test of [pref] is as if it were a number but the rest assumes that is
not correct as you would not say 'Dear 5'.
So if you want to test if it is text larger than zero characters long the
use this --
=Trim("Dear" & " " & IIf(Len([pref])>0,[pref],IIf(InStr([firstname],"
")>0,Left([Firstname],InStr([Firstname]," ")-1),[firstname])))

But if [pref] is a lookup field then you need to join a table to translate it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top