Missing Data after ODBC to Microsoft Access

[#1]

I'm using CR XI Professional, Version 11.0.0.895 and export the results of a report using an ODBC connection to a Microsoft Access Database. I have various date fields in the report and if the first record does not contain a date in the date field, all remaining records will display a blank instead of a date.

If I sort the report results before exporting to make sure there is data in the date field, results export properly to Access. This is not an acceptable solution as the report has multiple date fields.

If I do not sort and export to Excel, data exports properly so it seems to be either an ODBC issue or a Microsoft Access issue.

Does anyone have a resolution?

[Former Member · Accepted solution]

Let me start by saying that I've never tried going from CRXI to MS Access... It's always gone the other direction.

That said, it sounds as if Access is basing the import format on the 1st record. You may want to try setting the Import / Export Specifications in Access manually and saving it in the .mdb file.

[Former Member · Accepted solution]

Thanks for the reply. I'm not sure what you mean by:

"Import / Export Specifications in Access" Could you please explain?

[Former Member · Accepted solution]

Hey P,

I think you need to make some things clear. I think you're saying that:

-You have an ODBC connection.

-This ODBC connection is with Access.

-When you export the results of running the report, you export it to an Excel spreadsheet.

-When you sort the data, the date field of the first record is empty, and this causes the whole report to return 0 results.

Do you have a suppresion formula? It might not be in the section you originally intended it to be in.

Until then,

The Panda

[Former Member · Accepted solution]

No, what I am saying is I have an ODBC connection to an Access database. Within Crystal, I select the ODBC export option and select MS Access Database and then select my database. If the first record being written in Access has a blank date, the remaining records will also have a blank date.

If I take the Excel export option out of Crystal instead of ODBC, records are written correctly into Excel.

Another interesting happening is the order in which the records are written. They differ between Access and Excel. I get 2 entirely different results between the Excel and Access exports.

I hope the above is a little clearer. If not, let me know and I'll try to give you more detail.

"Pete"