Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select records with NULL date

Status
Not open for further replies.

2ffat

Programmer
Oct 23, 1998
4,811
US
I am trying to create a C8.5 report using SQL server 7. We need to see all records that have a null date. I have tried:
{PurchasedLogs.DispersalDate} = DateTime (0, 0, 0, 00, 00, 00), {PurchasedLogs.DispersalDate} < DateTime (0, 0, 0, 00, 00, 00) [per Crystal's web site KB], and PurchasedLogs.DispersalDate} <= DateTime (0, 0, 0, 00, 00, 00).
No records show up. I have turned &quot;Change NULL to default value&quot; on and off but still nothing. If I don't select any date, all records with or without a date show up so I know there are records without a date. James P. Cottingham

I am the Unknown lead by the Unknowing.
I have done so much with so little
for so long that I am now qualified
to do anything with nothing.
 
Hello James,
Try using this to find them:
length({PurchasedLogs.DispersalDate})<2 in your selection criteria
hope this helps!
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com
 
I had to try
Code:
length(ToText({PurchasedLogs.DispersalDate}))<2
because length expects a string and then set &quot;Change NULL to default value&quot; on to get it to work. Thanks :) James P. Cottingham

I am the Unknown lead by the Unknowing.
I have done so much with so little
for so long that I am now qualified
to do anything with nothing.
 
James,

you might also try the IsNull function. This function was available in CR7.0. Note that you will need to turn off the &quot;Change NULL to default value&quot; option for this to work.

Example:

IsNull({PurchasedLogs.DispersalDate})
 
Thanks, dalchri. Tried that but didn't work the way we needed it to.
James P. Cottingham

I am the Unknown lead by the Unknowing.
I have done so much with so little
for so long that I am now qualified
to do anything with nothing.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top