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

Please discuss general Delphi programming topics here.

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

Postby sirjeff » July 27th, 2004, 4:40 am

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 ...
sirjeff
Member
Member
 
Posts: 3
Joined: January 4th, 2004, 4:09 pm

Postby aspence » July 28th, 2004, 1:32 am

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.
- Arnold B. Spence
aspence
Active Member
Active Member
 
Posts: 13
Joined: July 11th, 2004, 8:50 am
Location: Wolfville, N.S. Canada

The following is the correct solution ...

Postby sirjeff » July 28th, 2004, 4:04 am

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 !!
sirjeff
Member
Member
 
Posts: 3
Joined: January 4th, 2004, 4:09 pm

Postby aspence » July 28th, 2004, 4:48 pm

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);
- Arnold B. Spence
aspence
Active Member
Active Member
 
Posts: 13
Joined: July 11th, 2004, 8:50 am
Location: Wolfville, N.S. Canada


Return to Delphi Programming

Who is online

Users browsing this forum: No registered users and 2 guests

cron