Using a / in a filter expression for a datefield ??

[sirjeff]

When I try and filter on a date field with a dateseparator of / I get an error message : Arithmetic in filter expressions not supported.

If I change the dateseparator to a - or a . then everything works ok !?!?!?!

Help ...

[aspence]

No matter how many times I encounter problems with dates and separators they never seem to go away. As an initial crack at this one though I'll ask if you are enclosing your filter text in quotes like:

SomeField > '2003/04'

Depending on the server, DB interface (driver) or locale settings, things can get confusing. '-' and '.' are common date separators for sql.

[sirjeff]

In theory when you use the filter command with Dates and Times, the date/time should be betewen ''. Even if the date's a variable. For example, the following is the correct way to code a filter with a date :

TForm1.Filter:= 'Date1 = ' + '''' + VarDate + '''';

We need 4 ' ('''') because to create a single quote as a character we have to have 2 single quotes together and then we have to have the character quote between 2 quotes to make it a character. Confusing but logical. The best example is the character 'c' is between 2 quotes which make it a string and not a variable. Now replace the character c by a quote (you need 2 quotes to make a character quote in a string and et voila you have '''' !

It took some time to figure out but I got it going finally. The main problem was that a . or - worked okay without the quotes but when the / was used as the dateseparator, the problems arose !!

[aspence]

Indeed.

This double quote thing can make for tricky typing and some think it reduces readability. When I have to create sql in code using variables that need to be in quotes I use the QuotedStr() function from SysUtils.

TForm1.Filter := 'Date1 = ' + QuotedStr(VarDate);