If you are using Sage 50 (PeachTree) on Windows 11 and your Crystal Report gets the SQL error below, I think I have a fix for you.

The error says:

Failed to retrieve data from the database
Details: ADO Error Code: 0x
Source: Pervasive PSQL OLE DB Provider
Description: [LNA][Pervasive][ODBC Engine Interface]Syntax error
[Database Vendor Code: 268435456]

The error means that Crystal is generating Pervasive SQL that has bad syntax. In the case I faced the same report would work on an older system but not on the Windows 11 system. Other reports were fine in Windows 11 but not this one.

First we were able to narrow it down to a subreport. Then I found that the subreport would work with no selection criteria so we knew it was an error in the WHERE clause. We took out each rule in turn to narrow it down to the Date criteria. The failure only occurred when there was a Date rule in the selection formula.

I took the faulty SQL that Crystal generated and tried running it as a command a few different ways until I figured out the Pervasive SQL was looking for a DateTime value instead of a Date Value. I changed the selection formula to use DateTime values and the syntax error went away. Here is the syntax I used with two date parameters:

{JrnlHdr.TransactionDate} in
DateTime({?FromDate}, Time(0,0,0)) to DateTime({?ToDate} , Time(0,0,0))

If you have a date range parameter you could use:

{JrnlHdr.TransactionDate} in
DateTime(Minimum({?DateRange}), Time(0,0,0)) to
DateTime(Maximum({?DateRange}), Time(0,0,0))

I can’t say for sure this error only occurs in Windows 11 since I found the exact same error in a forum question from 2018 (before Windows 11 was released).

Update – 6/24/2023:

In the forum discussion above a Sage specialist reports that the error is related to CR 2008 SP1, and that upgrading to CR 2008 SP2 solves the problem. You can check your version by looking up the full Crystal Reports version number on the [Help > About] screen.   If your version starts with  12.1 you are on CR 2008 SP1.  If it starts with 12.2 you are on SP2.   If your version doesn’t start with 12, then you aren’t using CR 2008. 

And thanks to John Dierkhising of Bottom Line Accounting for point this out.