Getting Crystal 9 to recognise blank dates in Visual Foxpro

[NickStiven]

I use a bespoke database built with Visual FoxPro. I have been using Crystal 6 and vpfodbc.dll (Version 6.1.8629.1) to read an employee table and report the birthdays of everyone who has not left. These people therefore are those where {staff.leftdate}= Date (1899,12,30), which I understand is VFP's way of reporting a blank date. Crystal 6 has no problems.

Crystal 9, however, resolutely refuses to understand Date (1899,12,30) or Date (0,0,0) and the entry is not blank (so I cannot use the IsNull function).

Anybody got any ideas what I'm missing?

Nick Stiven

 

[synapsevampire]

What does resolutely refuses mean, you get an error, it doesn't understand the date, or???

Right click the field and select browse data, it should show any default values.

I just tried:

{nodatehere.nodatehere} <> cdate(1899,12,30)

in the record selection formula and it worked fine using a MS ODBC Microsoft Visual Foxpro free table directory connection to a VFP 7 table from CR 8.5.

-k

 

[NickStiven]

Yes, according to colleagues with Crystal 7 and 8.5, these versions appear to behave like Crystal 6 when reading VFP blank dates. I rather wish I had upgraded to 8.5 instead of 9!

In answer to your question, Crystal 9 shows December 30, 1899 as a browse value and reports December 30, 1899 as the {staff.leftdate} for staff without a leaving date when asked to report for all staff (i.e. when no selection filters are applied).

When I apply the selection filter {staff.leftdate} = Date (1899,12,30) only the report and page headers print, but no detail entries appear, of ANY staff.

However your questions prompted me to start thinking laterally. I just now tried {staff.leftdate} <= Date (1899,12,30) and found, to my surprise, that I had achieved the effect I wanted: a listing of those staff without leaving dates. I then tried {staff.leftdate} < Date (1899,12,30) and again got only the headers printing, but no detail entries appearing of any staff.

So, perhaps Crystal 9 is a bit weird in this regard?

Thanks for your help.

Nick Stiven