Filtering for an empty field 1

[tmozer]

Crystal Reports 8.5 and Oracle tables.....

I have a field in a table that we are using to flag certain types of cases. Right now there are three different entries in this field (LABEXAM.Report Type). However the vast majority of the records in the database show this field as empty.

I can, using the select expert, easily filter for any of the three entries. But, I cannot for the life of me make it select empty/null/"" records. If, for example, I filter for NOT one of the entries ({LABEXAN.Report Type} <> "NONBIO") I get a list of the other two, but not the blank records. If I filter for null or "", I get nothing.

What CR/Oracle trick am I missing here??

 

[rosemaryl]

Did you try:

isnull({LABEXAN.Report Type})
or
({LABEXAN.Report Type} <> "NONBIO")

If that doesn't work, then what is in the field when it is "empty" ... is it a blank which means you could change your criteria to:

({LABEXAN.Report Type} <> '')
or
({LABEXAN.Report Type} <> "NONBIO")

If that doesn't work .. try posting more information such as crystal version, database type and driver used, and tables and any joins between tables.

regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rosemaryl]

Dear tmozer,

Sorry I see that you did post your version and database ... so used to typing that ... my apologies.

However, if my suggestions don't work it might be helpful to know the driver you are using.

regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

I've seen the same thing occur against Oracle databases because the field is not null, rather it's padded.

Also the connectivity matters, try to use the native connection, but if need be, use the CR supplied Oracle ODBC driver, not Oracles.

Try the following to filter for just empty values:

(
isnull({LABEXAN.Report Type})
or
trim({LABEXAN.Report Type}) <> ""
)

-k

 

[tmozer]

This worked:

Make sure that the Convert NULL Values to Default checkbox is checked in Report Options.

Trim({LABEXAM.Report Format}) = ""

 

[rosemaryl]

Dear Tmozer,

I am glad that it worked ... however I hate using the Convert Null Values to Default because I like working with my data exactly the way it is in the database.

With that turned off did the isnull(field) not work?

Regards,
ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[tmozer]

Correct. The filter gave me no records....... Very stange. I even tried with a formula and tested for the value. Same result.....

I guess Oracle must pad with something.....

 

[synapsevampire]

Oracle doesn't pad, but some applications do (such as Lawson).

You never did state what connectivity you're using, did you try changing it?

-k

 

[tmozer]

I'm not sure what you mean by the "connectivity" I'm using? My linking (all Left Outer)?

 

[synapsevampire]

Are you using ODBC or Native?

Crystal must use a driver to connect to a data source, adn the wrong driver, such as the Oracle ODBC driver as opposed to the CR supplied Oracle driver will cause strange results.

Native is faster and what I use and suggest.

Select Database->Set Location to learn what you're using. I think that there was a Change Database Driver option in CR 8.5 as well which will tell you the current DLL being used.

-k

 

[tmozer]

Under the "Convert Database Driver" tab it shows (convert from):

pdsora7.dll (Oracle 7.x)