Show Records that have blank fields 1

[CrystalBox]

I use Crystal XI along with MS ACCESS.

I'm having trouble trying to show records when a particular database field is blank. Specifically, I'd like to produce a report that shows cases that are "Open" (pending). The only indicator of a "Open" case is when the "completed" date field is blank. I've been able to show both "completed" and "Open" (completed) on the same report using an left outer join, but can't figure out how to just show the cases that are open based on the blank date field.

My report is grouped by "Investigator". I would like to show just the "Open" (pending) cases each investigator has.

Any help you can give me is appreciated.

 

[CR85user]

It depends if your blank field is null or just blank.

Test for both and as with all null checks the null test comes first.

//selection criteria
isnull({table.date}) or trim({table.date}) like ''
and [i]whatever the rest of your selection criteria is...[/i]

'J

CR8.5 / CRXI - Discovering the impossible

 

[CrystalBox]

I'll try that. Thanks

 

[CrystalBox]

It didn't work for me. The "Trim" function doesn't work on date fields. Here's an example of the report output I'm getting.

Case No. Classification Date Completed

01-001 A 4/1/09
01-002 C 4/2/09
01-003 D 4/6/09
01-004 E 4/7/09
01-005
01-006
01-007
01-008
01-009
01-010


Basically, this is what I'm getting on my report. I'm using the left outer join to see all my field data. I would like to filter out the completed cases (records) and just show the cases that have a blank "classification" and
"Date completed" field.

 

[lbass]

(
isnull({table.date}) or
{table.date} = date(0,0,0)
)

-LB

 

[CrystalBox]

Thanks for your reply LB. I tried your formula and its giving me the open (pending) cases that I need, but its also giving me a few cases with the dates filled in (completed). A little baffling for me. Any ideas?

 

[lbass]

Please show your entire record selection formula. The formula I showed you would not return any dates that were completed.

-LB

 

[CrystalBox]

My record selection:
(IsNull({EXP_COMPLETED.DATE}) or {EXP_COMPLETED.DATE}=Date (0,0,0))and
{EXP_INTAKE.Secondary Investigator} in ["PETER, "PAUL", "MARY"]

I'm not sure if this means anything, but more than one completion date can be entered on one case.

Example:

Case No. Classification Completed date
01-001 A 3/15/09
01-001 B(changed) 3/18/09

If someone was to enter a blank space in the above fields, would that create the discrepancy?



If some

 

[lbass]

There is no way (that I can think of) that you would get a visible result in the completion date field using the given formula--unless you are displaying a different completion date field from that used in the selection formula.

-LB

 

[CrystalBox]

That crossed my mind LB, but if that were the case, I would be getting more errors. I got about 7 discrepancies, 2 in 2004, 2 in 2005, 2 in 2007 & one in 2008. The strange thing about that is that we had a few hundred cases each year so why are those few cases showing up on my report. Something to ponder. Thanks LB. I'm going to look a little further to see what I could find.

 

[CrystalBox]

LB, while trying troubleshoot my problem, I discovered something peculiar, I removed the left outer join, leaving the selection formula you gave me in place
(IsNull({EXP_COMPLETED.DATE}) or {EXP_COMPLETED.DATE}=Date (0,0,0))and{EXP_INTAKE.Secondary Investigator} in ["PETER, "PAUL", "MARY"]and I got only the 7 discrepancies that I mentioned and not the cases with the blank date field. Does that make sense?

 

[lbass]

I would have to know what tables you are using in what direction they were linked. If, however, you require that a date field be null or be equal to date(0,0,0), you will not get any other date in that field, regardless of joins, so there is something else going on that we don't know about. I'm guessing you did not show us your actual selection formula either, so there could be something wrong with that.

-LB

 

[CrystalBox]

This is my actual selection formula - I simplified the report and removed two of the four tables. I left the table that contains case info and the table that contains the classification and completion date.

(IsNull ({EXP_COMPLETION.DATE}) or {EXP_COMPLETION.DATE}=Date(0,0,0))

The odd thing is that I'm getting about 40 cases, all before 2009 and all the incompleted cases are in 2009. Does it matter that the completion field type is a Date & Time field?

When I use the left outer join link it gives me the 40 cases plus the 2009 incompleted cases.

 

[lbass]

How are the tables linked (from what table to what table)?

What is the name of the date field you are displaying in the detail section?

Please show a sample of the data that shows some of the 40 cases and also some of the incompleted cases, and identify each field using the {table.field} convention.

-LB

 

[CrystalBox]

I have two tables, the Main table (EXP_INTAKE) and Classification table (EXP_CLASSIFICATION). The Main table is linked (left outer join) by the primary key number, "INTAKE" To the primary key number of the Intake table, "CL_INTAKE". I'm only displaying the EXP_INTAKE.INTAKE_NUMBER (string) and date field, EXP_COMPLETION.DATE(DateTime)in the detail section.

The EXP_INTAKE.INTAKE_NUMBER is only being displayed on my report. I randomly checked some of the 40 cases and found some similarities. First let me explain how the "EXP_CLASSIFICATION" table input screen works. There are 3 fields, "EXP_Classification", a drop-down text field, and the "EXP_COMPLETION.DATE" field. The table allow us to enter several row entries. I found that there are blank rows before an entry on several of the cases I look at. Users will sometimes inadvertently hit the enter key, creating a new second row and begin typing there. Here's an example of what I'm talking about. This is one case.

Classification Text Field Completion Date
(blank) (blank) (blank)
A Change 4/1/09

I found several of the cases that match this example. Could it be that the "IsNull" function formula is just doing its job and reporting on those blank rows?

 

[lbass]

I don't see why hitting a return key would create an entirely separate detail row. When you look at the above data, if you look to the left at the gray area, what report section do you see? Is there one detail label per "line" or only one detail section with two lines? A null check would be false even if the record contained just a return.

-LB

 

[CrystalBox]

I'm showing one detail per line. Just looked over my previous threads to you. I see where I failed to explain myself. When I said hitting the enter key creates a new row, I meant when entering data on our ACCESS database input screen. Sorry for the confusion.

 

[lbass]

I'm sorry, but I don't think I can help further. I can't see how you could possible get a date field to display with your current selection criteria.

-LB

 

[CrystalBox]

Thanks for your generous help LB. I think I'll take this one up with our database programmer. Have a great day!