Field display on report question?

Hi,

Using VFP 9.0. I'm designing a simple report for one of my tables and I'm
seeing something that seems odd to me. The field expression is:

alltrim(lname) + ", " + alltrim(fname)

So I should get 'lname, fname' on my report.

It seems to be working except where fname is null, then I just get .NULL. on
my report. I would have thought that if it were null then I'd just get
'lname, ' out on my report but instead I only get .NULL.. Can anyone shed
some light here?

Thanks,
Linn

Bernhard
Mon Feb 06 16:35:45 CST 2006

Hi Linn

>
> alltrim(lname) + ", " + alltrim(fname)
>
> So I should get 'lname, fname' on my report.
>
> It seems to be working except where fname is null, then I just get .NULL. on
> my report. I would have thought that if it were null then I'd just get
> 'lname, ' out on my report but instead I only get .NULL.. Can anyone shed
> some light here?
Basically, any expression, where a .NULL. value is involved, results again in a
.NULL. value:
5 + .NULL. => .NULL.
"string" + .NULL. => .NULL.
x > .NULL. => .NULL.

There is a special function, NVL(exp1, exp2) for this case.
It is equivalent to iif(not isnull(exp1), exp1, exp2)
In your case you would use it like this:
alltrim(nvl(lname, "")) + ", " + alltrim(nvl(fname, ""))

Regards
Bernhard Sander

Cindy
Mon Feb 06 21:07:08 CST 2006

One detail, the OP will want to deal with the comma if the LName is null and
therefore blank.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Bernhard Sander" <fuchs@individsoft.de> wrote in message
news:uIpit22KGHA.668@TK2MSFTNGP11.phx.gbl...
>> alltrim(lname) + ", " + alltrim(fname)
>> It seems to be working except where fname is null,

> alltrim(nvl(lname, "")) + ", " + alltrim(nvl(fname, ""))

Bernhard
Tue Feb 07 07:41:40 CST 2006

Hi Cindy

>>> I would have thought that if it were null then I'd just get
>>> 'lname, '

>> alltrim(nvl(lname, "")) + ", " + alltrim(nvl(fname, ""))

> One detail, the OP will want to deal with the comma if the LName is null and
> therefore blank.

In which other way do you think the OP wants to deal with the comma than what he
will get with my proposal?

If you want the comma to be only there if both, lname _and_ fname are not empty,
there need to be more program logic. But the .NULL. issue must be solved in a
similar way.

Regards
Bernhard Sander

Linn
Tue Feb 07 09:16:49 CST 2006

Thanks Bernhard, Cindy, nice discussion. Here's how I handled it:
In the Field Properties - Expression:
ALLTRIM(NVL(lname, "")) + IIF(ISNULL(fname), " ", ", ") +
ALLTRIM(NVL(fname,""))

and in 'Print only when expression is true':
NOT ISNULL(lname)
and I turned on 'Remove line if blank."

Thanks for the tip Bernhard,
Linn

"Bernhard Sander" <fuchs@individsoft.de> wrote in message
news:OOHx6w%23KGHA.2124@TK2MSFTNGP14.phx.gbl...
> Hi Cindy
>
> >>> I would have thought that if it were null then I'd just get
> >>> 'lname, '
>
> >> alltrim(nvl(lname, "")) + ", " + alltrim(nvl(fname, ""))
>
>> One detail, the OP will want to deal with the comma if the LName is null
>> and therefore blank.
>
> In which other way do you think the OP wants to deal with the comma than
> what he will get with my proposal?
>
> If you want the comma to be only there if both, lname _and_ fname are not
> empty, there need to be more program logic. But the .NULL. issue must be
> solved in a similar way.
>
> Regards
> Bernhard Sander