Record Selection with LastFullMonth stopped working

[hwwagen]

I have a report that was created in a very old version of Crystal Reports that used a record selection of:

{timesheet.date} in LastFullMonth

This worked well, but since upgrading to Crystal Reports 2008 it no longer returns any records. The SQL generated by this is:

SELECT `project`.`project_name`, `timesheet`.`date`, `timesheet`.`hours`
FROM `timesheet` `timesheet` INNER JOIN `project` `project` ON `timesheet`.`project_id`=`project`.`project_id`
WHERE (`timesheet`.`date`>={d '2008-11-01'} AND `timesheet`.`date`<={d '2008-11-30'})
ORDER BY `timesheet`.`date` DESC

I don't know if it makes any difference, but the database containing the fields is an older version of Foxpro. The field timesheet.date is a date field.

I thought that there might be something else wrong with the report so I began creating a new report from scratch and get the same results in the new report as soon as I add the record selection formula above. Can someone please point me in the right direction for correcting this?

 

[DataDog]

Eliminate "{timesheet.date} in LastFullMonth" from the select expert to see if it will bring back ANYTHING. If that doesn't bring back anything, then the problem has something to do, (I would guess) with your linking. Also, are you sure that the field is a date datatype?

DataDog
If God wanted us to count in Hexadecimal, then why did he only give us A fingers?

 

[hwwagen]

Crystal does report that the field is a date field. Removing the selection formula returns all records.

 

[dgillz]

Create a true/false formula:

{YourDateField} in LastFullMonth

Place this on the report and run it. Does it properly indicate which records are in the last full month? This would be November 2008 records by the way.

As an aside, you should not expect the record selection formula to translate into the SQL WHERE clause. This CAN happen, but do not expect it.

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[hwwagen]

I have done some more investigation into this. Removing the record selection formula returns all dates and they look like valid dates. Changing the formula to be {timesheet.date} < CurrentDate returns records (that appear to be the same) with the highest date being in 1899! There seems to be some issue with the way that FoxPro is storing the dates that are retrieved into this report.

 

[hwwagen]

dgillz,

I followed your suggestion and added the formula to the report on each detail line. It accurately displayed true or false for the displayed dates. However, when I put this same formula into the record selection formula I have no records returned. When I change the record selection to check for dates less than currentdate all detail lines accurately display false for the formula because my most recent date is August 12, 1908.

BTW, when I browse the data in CR I get dates that look like;
6/13/190, 8/3/200, 5/22/522, 11/18/1048, etc.

Everything looks normal and displays as expected in FoxPro.