Select Expert with date fields in Visual FoxPro database

[Mike Lewis]

I'm using Crystal Reports XI (and have also reproduced this problem in CR 9), with the latest Microsoft OLE DB Provider for Visual Foxpro.

When I try to use the Select Expert to make a selection based on a Date field, the OLE DB Provider reports an error: Column DATETIME not found (Native Error: 806).

The View SQL window shows something like the following code being sent to the database:

SELECT <various fields>
FROM   MyTable
WHERE  MyTable.MyDateField =CONVERT(DATETIME, '01-07-2011', 110)

Clearly, CR is trying to convert a character string representing a date to a datetime. This syntax would work fine in SQL Server, but it is not recognised by Visual FoxPro, which does not have the CONVERT() function. Hence the error.

I can work round this by treatng the field as a datetime rather than a date, thus avoiding the need for conversion. To do so, I edit the selection formula, from this:

{MyTable.MyDateField} = Date (2011, 01, 07)

to this:

{MyTable.MyDateField} = Date[b]Time[/b] (2011, 01, 07[b], 00, 00, 00[/b])

That works OK. The trouble is that I would have to adjust the formula in that way every time I want to reference any date value in any selection formula, which is quite onerous.

I wonder if anyone has come up against this problem before, or can suggest a better solution.

Thanks in advance.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Since I didn't get any replies to this question, I will assume that nobody else has been affected by it.

I know that the Visual FoxPro OLE DB provider isn't a very common data source for CR users, but I would have thought that this issue would have cropped up before. After all, I'm simply trying to filter on a date field, which must be a completely normal thing to do.

Ah, well. Not to worry. I'll just have to continue with the rather clumsy workaround.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy