Crystal not finding 31st day of months on some systems

[harrietohs]

Crystal 8.5 Professional Edition

I have a report with the following formula:

{activity.activity_type} = "INV" and
{activity.new_date} <={?end date} and
isnull({activity.complete_date}) or

{activity.activity_type} = "INV" and
{activity.complete_date} in {?Start Date} to {?End Date}


On only SOME computers, the data from the 31st day of a month is not included. I have exhaustedly checked that the correct date parameters are being entered and even printed a detailed report for comparison. What I found was all records dated the 31st were missing on the "bad" systems. So, my question is, what would make one computer system not report the 31st. All the other, short months, are counting fine.

Harriet

 

[dgillz]

They are getting different results on the sama dataset?

Put your isnull() test at the very beginning and try this again.


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

http://www.trianglepartners.com

 

[harrietohs]

Yes, different results from same dataset.

I moved the null test to the beginning, but still not picking up the 31st day.

 

[vidru]

What database-type/connectivity are you using? It could be a connectivity issue. If you're using ODBC, for example, make sure you're using the same version of MDAC on all machines.

Also, you could try tightening up your selection formula a bit to make sure the OR isn't causing any confusion by enclosing each section outside of the OR in parentheses. Or, you could try something like this:

(
{activity.activity_type} = "INV"
)
and
((
{activity.new_date} <={?end date} and
isnull({activity.complete_date})
)
or
(
{activity.activity_type} = "INV" and
{activity.complete_date} in {?Start Date} to {?End Date}
))

-dave

 

[harrietohs]

I tried your formula, but nothing changed.

Before I check the MDAC version, does it make a difference that some of our systems are still on Windows 98, while others have now been converted to Windows 2000?

Also, If I run the report on the "wrong" system, and include the first day of the following month in the select critera (i.e. March 1, 2004 to April 1, 2004) the results from the "wrong" machine are equal to results for March 1-31 on the "right" machine.

 

[dgillz]

Are you literally entering a value for your date parameters or are you passing a value to the parameters programatically somehow?



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

http://www.trianglepartners.com

 

[harrietohs]

I am literally entering the start and end dates.

I showed the SQL on both machines to see if the selection formula looked any different, but they appeared the same.

 

[dgillz]

This is the wierdest thing I can ever remember on this forum!

Just a stab in the dark, but are the system dates OK on all workstations?



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

http://www.trianglepartners.com

 

[lyanch]

Are these all running from the designer? It used to be that you had to make sure that datetime < date was "date at end of day". It looks like some systems are using this old formatting? Do all the systems have the same patches applied?

Is it only when < is used or does <= get the same bad treatment?

Lisa

 

[lyanch]

A couple other questions.

Is it missing the 31st day only when the "date to" is the 31st?

Or is it the 31st no matter if it is the beginning, middle or end of the range..

Or is it the last day of the report no matter what the range?

Lisa

 

[dgillz]

just for kicks, email me your report, with saved data. I have to see this.

dgilsdorf@trianglepartners.com

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

http://www.trianglepartners.com

 

[dtempleton]

Sounds like {PRACTWORK.DATE} is a Date-Time field, and the time changing to midnight on the bad machines. In your reporting tab for options (on both the good and bad machines, do they both have "Convert Date-Time Field to Date-Time" ?

 

[dgillz]

I don't know what field you are talking about, but the fields are all date data type.

Try moving the cross tab objects into the report footer instead of the report header and see if that makes a difference.



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

http://www.trianglepartners.com

 

[dgillz]

Can you confirm that the ODBC DSN for each workstation is actually pointing at the same database?

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

http://www.trianglepartners.com

 

[dtempleton]

Sorry about my previous message. It was meant for a different (but very similar) thread.

Can you give an example of the data from {activity.new_date} and {activity.complete_date} ?

 

[harrietohs]

I am leaving until next Tuesday, but will continue to try and unravel this mystery when I return.

To answer your questions and clarify things:

Yes, all the systems are using the same System DSN and pointing at the same database. This database is part of Humane Society shelter operations software (Chameleon) that uses Crystal as it's report engine.

Crystal does not reside on each machine. For general users, the reports are accessed via the software app. (Yes, I am also in contact with the software company, who also are puzzled.)

I can't even find a relation between operating system and this error. Some Win 98 get it right some wrong, same for Win 2000.

Indeed, this is truly a challenge.

 

[dgillz]

Have a good one....I cannot wait to see what this resolution ultimately turns out to be.

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

http://www.trianglepartners.com

 

[harrietohs]

I have now tried all of the suggestions offered, including updating the "bad" systems to MDAC 2.8, moving the cross tabs from header to footer, moving null tests around, etc. etc. and still the problem persists. I did contact the software support for Chameleon (the app from which the report is actually run) and they also thought it might be MDAC. So, sadly, I am back to square one.


Any other thoughts out there?

Harriet

 

[harrietohs]

Found the solution!!!
I changed the offending Crystal report's options to show dates as date-time instead of date, although the times were in no way used by the report, and some systems were already showing the correct data. Voila! All the "bad" (and "good")systems are now showing the correct data.

Now, what I'd like to know is why?