cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports - Data lost before 10/1/2023

siz1000
Discoverer
0 Kudos

We have a Crystal Report which runs against a spreadsheet downloaded from a web application. The date field defaults to type "General" in Excel.

The report runs correctly on one machine, but on the other machine it appears to loose\ignore content in the spreadsheet with date values older than 10/1/2023.

Both have the same version of MS Office 365 64 bit with Crystal Reports 2020. The file is stored on a Windows 2016 Server and we are running the report from either a Windows 10 Desktop or another Windows 2016 Server via Remote Desktop Client. The later is the issue.

Date formats are identical on both machines.

Accepted Solutions (0)

Answers (3)

Answers (3)

siz1000
Discoverer
0 Kudos

We have converted the calculations from text to date calculations and this seems to have resolved the issue.

We think that the system was interpreting 10 as less than 09, effectively making this an alphanumeric comparison error.

Thank you for your help.

JWiseman
Active Contributor
0 Kudos

I don't see any issues on my two machines using the same data that you posted: General formatting in Excel, coming into CR as String 255 via MS ODBC Excel driver. My server is an updated 2019 Win Server and not 2016 so that could be a difference.

Suggestion to use a formula instead of the field itself to see if that works in all values...e.g. Use something like the following and check to see if you still get values dropping off.

if isdate({Sheet1_.Your_Date_Column})
then date({Sheet1_.Your_Date_Column})
JWiseman
Active Contributor
0 Kudos

p.s. I'm using MS Excel driver version 16.00.4999.1000 in Win Server 2019. That version might be the one here.

JWiseman
Active Contributor
0 Kudos

Hi Steve,

  1. How are you connecting to the spreadsheet? Is it via ODBC using the MS Excel driver to .xls or ODBC using another driver to < .csv | .txt >?
  2. Also, if you look at the report in CR on both machines, with the File Explorer > Database Fields > set to Show Field types, is there a difference between the 2 machines?
  3. Can you post several of the date values that are in the spreadsheet? A combination of good & problematic values would be best.
siz1000
Discoverer
0 Kudos

Hi Jamie

1 ODBC using the Excel driver. FWIW, I just noticed the download is an older XLS format if that means anything

2 Activity Date: String [255] on the RDS machine, I do not have access ATM to the other PC, but think we looked at this as being the same.

3 I have copied cell values which appear to get added as a PNG, not clear if this is what you wish to see. Went back and added the text from the entry line in excel above the worksheet.

Bad:

08/31/2023

09/19/2023

Good

10/06/2023

10/13/2023

I just realized that there are leading zeros in the spreadsheet. They seen to disappear if I click in and out of the edit line, but after saving this does not impact the report.