Tim, thanks for using and supporting OOo :)
Do you have the possibility to generate ODBC logs for the process of
executing the filtered query? If so, could you please generate them
and attach them to this issue?

Marc, do we have a FoxPro database to check this?

Can't generate logs that I know of.  Same results in ODBC to an 
Advantage and Visual Fox Pro data.

I was able to query the date field on a Visual Fox Pro database after 
asking a programmer for the proper date format. See Sample below.  
The issue is that OOo doesn't allow that format for dates and changes 
it to {D '2002-10-01' } which is ignored by the database.  In 
addition, the between command gives me a syntax error.  I have to 
switch off the design view and run the SQL command directly. I can 
enter BETWEEN '10/01/2002' AND '10/31/2002' in design view and then 
switch to SQL view and change the ' characters to { or } this is a 
work around.

Sample of format that works with Visual Fox Pro Database in SQL view 
only: BETWEEN {10/01/2002} AND {10/30/2002}

As for the Advantage database, the following format works in design 
view: BETWEEN '10/01/2002' AND '10/31/2002' 

well, that may be a problem ...

The formats you gave are not SQL-92 compliant, so OOo can not easily
use them on _any_ ODBC data source (because they are
database-specific, and OOo is to expected to work with more than
FoxPro and Advantage :).

Instead, OOo uses the {D...} notation, because this is the required
ODBC syntax, and in this sense, it's a bug of the ODBC drivers not
recognizing it.

We could perhaps add code for optional alternative date formattings ...

Thanks for your help.  The programmer told be that the Visual Fox Pro 
data was a bit unique.  As for the second format 

BETWEEN '10/01/2002' AND '10/31/2002'

It DOES work in design view and SQL view OOo as shown.

Tim, so the bug "vanished" for you?

It has sort of "vanished". I think what would be good is if OOo 
warned you of non-compliant formulas, but gave you the option to 
launch the query anyway.

Or... simply add the ability to accept:
BETWEEN {10/01/2002} AND {10/30/2002}

OOo already accepts: 
BETWEEN '10/01/2002' AND '10/31/2002' 

Or... include the workarounds above to the help section.  (This is 
probably the easiest solution)

Hi,

So we need a section in the datasource setting to specified more
settings for a datasource to 'workaround' those issues.

Bye Marc

FT: re-targeted to Office later

change subcomponent to 'none'

FT: Re-assigned to requirement default user