checking for blanks

[rq]

I am using Crystal 8.0. I would like to create a formula field that will determine if a database field (data field type is date or character) is blank or empty. I tried the following for character type fields to test:

ToText ({ado.fieldname}) like '%'
ado.fieldname = ' '

What would you suggest?

 

[dgillz]

use the Isnull() function to test for nulls. Also, always place your null test as the first line of code in your logic.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[synapsevampire]

this is database dependent.

For characters, you might try:

if isnull({table.field})
or
{table.field} = "" then
"I'm blank"
else
{table.field}

For dates, check with the dba to see if there is a default for no dates, otherwise just try:

(null sanity check the date)
if isnull({table.datefield})
or
{table.datefield} <= cdate(#1/1/1901#) then
&quot;bad date&quot;
else
&quot;OK&quot;

You might be better served to describe why you are checking for this.

-k

 

[rq]

I tried synapsevampire's suggestion for character fields and it didn't work. Since you said it is database dependent, the fact that data comes from SQL Server 2000's Select statement and looked upon as an ADO, will that have any bearing to why I'm not getting the results I expected per your suggestion?

 

[synapsevampire]

Perhaps the character field isn't empty, rather it has spaces in it?

if isnull({table.field})
or
trim({table.field}) = &quot;&quot; then
&quot;I'm blank&quot;
else
{table.field}

This is a standard check for a blank field, nothing fancy or unusual here.

You should be placing this code in a formula (Insert->Field Object->right click Formulas->New), and displaying the formula in lieu of the field.

-k

 

[dgillz]

Also try this:

Length({FieldName})=0

I have had some unpleasant experiences using IsNull() on a btireve database, and this was my solution. I don't know if this will be an issue or not but its worth a shot.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com