IIF Function

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.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Roger Bell" wrote in message
news
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.

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.

--
Build a little, test a little.


"Roger Bell" wrote:

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.