Check for empty field in DBF fails

[Andrew111]

I must be overlooking something obvious. But I'm new to Crystal and can't spot it. What I'm trying to do is us an iif() statement to generate a "zz" if a field is blank. But what I get is a blank result when the field is blank.

I've tried

iif( isnull({WILLIAMS.TO_ART}), "zz", {WILLIAMS.TO_ART})
iif({WILLIAMS.TO_ART} = "", "zz", {WILLIAMS.TO_ART})
iif({WILLIAMS.TO_ART} = " ", "zz", {WILLIAMS.TO_ART})

I've also tried changing these settings:

Convert database NULL values to default
Convert other NULL values to default

The database is a DBF created by Visual dBASE 5.7. I hooked to it using the xBase choice under Create New Connection.

 

[lbass]

I think there are occasional problems with iif statements in CR. Instead, just use:

if isnull({WILLIAMS.TO_ART}) then "zz" else
{WILLIAMS.TO_ART}

-LB

 

[lbass]

PS. Make sure you do NOT have the convert to default options checked in the report options.

Also, this assumes that your field is, in fact, null, and does not contain spaces. To check for spaces as well, you could use:

if isnull({WILLIAMS.TO_ART}) or
trim({WILLIAMS.TO_ART})= "" then "zz" else
{WILLIAMS.TO_ART}

-LB

 

[Andrew111]

Thanks. That got my report working. And thanks for the reminder about checking for spaces. I'm still trying to uncover how Crystal behaves around spaces and nulls (also times and dates). I think running some tests will help me.

 

[Turkbear]

Hi,
One thing to keep in mind is that blanks are not the same as NULLs ( in Crystal and in many database engines)..

A NULL means, usually, that the value is undeterminable, so no information about it can be obtained therefore no comparison tests can be used, except to check to see if it is,in fact, NULL.
That is why a 'special' testing format is needed ( The IsNull function in Crystal for instance)..NULLs cannot even be compared to themselves or other NULLs.

A blank, on the other hand, means that no data is in that field..That is 'knowledge' about the field that is not available from a NULL field..

It can get almost Zen-like when dealing with NULLs, so as a practical matter, it is only necessary to account for them, not understand them..

Enjoy...








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Andrew111]

>One thing to keep in mind is that blanks are not the same >as NULLs ( in Crystal and in many database engines)

Thanks. This is one of the first particulars I try to learn about a program (how it handles NULLs). There is so much difference out there, and I'm not up to speed with Crystal.

And working with DBF adds a different wrinkle. The file version I'm using does not support NULLs versus empty space versus space. And so far it looks like any type of blank text field in a DBF ver. 4 is seen as a NULL by Crystal.

 

[synapsevampire]

Which connectivity are you using? Stating create new datasource doesn't quite describe it, you can use either ODBC or a direct connection under the Database files.

The ODBC may treat these differently than the direct file access.

At any rate, using LB's example will allow for both.

-k

 

[Andrew111]

Which connectivity are you using? Stating create new datasource doesn't quite describe it, you can use either ODBC or a direct connection under the Database files."

I wish I knew how to find that out. All I know is I'm using the Standard Report Creation Wizard and picking xBASE to start the connection.

"At any rate, using LB's example will allow for both."

Good to know. I would prefer to make the reports to cover both bases. That way I get into the best habits.

This is going to be slow-going, though. I have not had time to use Crystal since my previous posting.