Date parameter not making it to SQL (CR 8/UVODBC)

[Bontebok]

Hi Everyone,

I have a Crystal Report written in version 8.0 that is causing me trouble. The backend database is UniVerse on an RS/6000 interfaced by an ODBC driver. This has been working for sometime with no problems.

I was asked to add a date parameter so that they can provide a date range that they want the report data for. Simple enough, done this many times before. This should result in adding extra statements in the WHERE clause to filter the records as normal.

However, in this particular report in combination with this ODBC driver, the SQL statement generated by Crystal is missing the date value! It is replaced by a single question mark (?). However, when the report is run on another copy of Crystal here, the date is generated in SQL just fine. It seems to be limited to working on two machines and failing on two others (one being a server and the other being my workstation).

Here is a clip from the sql's where clause.

This is the non-working one:

TR_ORDER_RANGE_11."DAY" >= ? AND
TR_ORDER_RANGE_11.&quot;DAY&quot; <= ?

This is from the working one:

TR_ORDER_RANGE_11.&quot;DAY&quot; >= {d '2002-09-01'} AND
TR_ORDER_RANGE_11.&quot;DAY&quot; <= {d '2003-02-28'}


This is very strange to me, what DLLs can I check that are responsible for building the SQL statement/working with the selection expert/determining compatibility with the ANSI SQL from the ODBC driver? I am not able to compare versions of runtime and DLL on the working machine until tomorrow. I'm hoping someone has come across this before and can tell me what DLL versions I should be using or what hot fixes/patches I should install.

Thanks

 

[synapsevampire]

Applying hotfixes may help, but I'd be more suspicious of the ODBC drivers or differences in the design of each report.

Please post the record selection formula (report-Edit Selection formula->Record).

-k

 

[Bontebok]

Applying the latest Database Hotfix from Crystal Decisions solved the problem.

I am still not sure why the query was failing, but I think it had something to do with Crystal setting the wrong datatype to the ODBC driver for the parameter markers. I believe this happens when you have values from the Selection Expert of many datatypes.

The parameter marker system CR 8 used was the '?', however when you upgrade to the latest Hotfix, the parameter marker system is converted to curly braces as shown in the paste in my initial message.

For now, I need to determine if Hotfixes can be removed so that I may apply it to a non-development server.

Thanks