Date Problems: Reports need to run with VFP and SQL Server 1

[meryls]

I have over 180 reports that are launched via a VB application using a Visual FoxPro database. The application will now also support SQL Server, based on the needs of our customers.

All of the current reports use Date (not DateTime) fields. The reports are handed a recordset and do not access the database directly.

Now that we are testing with SQL Server, none of my dates are being displayed and date fields used within formulas and groups are just causing the reports to bomb.

Since I need to support both databases, I don't know what to do. If I convert all my dates to datetime for the fields to match the SQL, they don't work for the Visual FoxPro.

Does anyone have a solution? I am using Cristal 9, but will upgrade to Crystal XI if it will help.

Thanks.
Meryl Smith

 

[vidru]

In CR 8.5 and lower, in Report Options you could update a setting to change DateTime fields to Date. I don't see this option in CR 9 from the designer, and don't have CR XI here to see if it's there either.

That said, you can set it at runtime. Here's the ultra-simplistic version of the code using the CR9/RDC/VB6:

Dim ap As New CRAXDRT.Application
Dim rpt As CRAXDRT.Report
Set rpt = ap.OpenReport("c:\dt.rpt")
[b]rpt.ConvertDateTimeType = crConvertDateTimeToDate[/b]
CRViewer91.ReportSource = rpt
CRViewer91.ViewReport

-dave

 

[meryls]

Dave,

I can't thank you enough. Your solution has enabled us to deploy our project in support of both VFP and SQL.

I don't know why Crystal took that out of CR 9 and will post more info if I find it is back in Crystal XI when I upgrade.

Thanks again, so much!

Meryl

 

[vidru]

Glad it worked for you. The only problem with this is that in the future, you might actually need the times associated with the dates in the SQL Server db for reporting.

-dave