Filter by date in DBGrid using XE3

This piece of code was working fine when I used a database with all fields set to short Text, I could filter by date as it was text but I couldn't sort by date because it was just numbers it sorted as numbers not as a date. I decided to change the date field to Date/Time. Now sorting works great but when it comes to filtering it comes back with this error;

"Item cannot be found in the collection corresponding to the requested name or ordinal"

The code is as follows;

procedure TForm2.FilterByDateButtonClick(Sender: TObject);
begin
MonthCalendar1.Visible := False;
FilterByDateButton.Visible := False;
FilterEdit.Text := DateToStr(MonthCalendar1.Date);
FilterByComboBox.Text := '';
CountryClick.Visible := False;
CountryComboBox2.Visible := False;
ADOTable1.DisableControls;
FilterByComboBox.Text := '';
label3.Caption := DateToStr(DATE);
try
ADOTable1.Filtered := False;
// ADOTable1.Filter := FilterString + ' LIKE ' + QuotedStr('%'+FilterEdit.Text+'%');
ADOTable1.Filter := DateToStr(DATE) + ' LIKE ' + QuotedStr('%'+FilterEdit.Text+'%');
ADOTable1.Filtered := True;
if ADOTable1.Eof = True then
begin
FilterEdit.Text := '';
ShowMessage('No Record Found.');
CountryClick.Visible := false;
FilterByDateButton.Visible := False;
MonthCalendar1.Visible := False;
FilterByComboBox.Text := '';
FilterEdit.Text := '';
ADOTable1.DisableControls;
if FilterEdit.Text = '' then
begin
ADOTable1.EnableControls;
ADOTable1.Filtered := False;
exit
end;
try
ADOTable1.Filter := DateToStr(DATE) + ' LIKE ' + QuotedStr('%'+FilterEdit.Text+'%');
// ADOTable1.Filter := FilterString + ' LIKE ' + QuotedStr('%'+FilterEdit.Text+'%');
finally
end;
FilterByComboBox.Text := '';
Exit;
end;
finally
ADOTable1.EnableControls;
end;
end;

Debugger Exception Notification says "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." which comes from Data.Win.ADODB.

I thought using "DateToStr(DATE)" would work but obviously not.

This is the final issue I have with my program so any help on this would be greatly appreciated,.

regards
Steve

Comments

  • steve53 wrote:
    ADOTable1.Filter := DateToStr(DATE) + ' LIKE ' + QuotedStr('%'+FilterEdit.Text+'%');


    I thought using "DateToStr(DATE)" would work but obviously not.
    OK, Think what you are asking the filter to do. You want it to find data where the value in some field is like a pattern that you supply.So your filter needs to look like this:
    FieldName LIKE "RequiredValue"
    Where in your code do you tell it which field you want to match the pattern?
    Unless you are very lucky, DateToStr(DATE) is never going to match the name of a field in your database:)
    If your date field in the database is called ContactDate then use that
    ADOTable1.Filter   := 'ContactDate LIKE ' + .....
    
  • OK so I was right at the start, the Reason I am using "FilterString" is because I am filtering by 1 of 5 fields ie;

    date
    frequency
    mode
    station
    country

    I use a comboboox to select 1 of these fields, they are all text format except date which is date format and all the fields except date work fine. That is why I am using the code;

    ADOTable1.Filter := FilterString + ' LIKE ' + QuotedStr('%'+FilterEdit.Text+'%');

    So if I am trying to filter say by frequency, I select "frequency" from the combobox list, it then passes the word "frequency" to FilterString, when I type in the frequency in the FilterEdit.Text box it then filters ok.

    For some reason this didn't work for the date field and I think I was trying to over complicate things,

    ADOTable1.Filter := 'datetoday LIKE ' + FilterEdit.Text;

    This did the trick, how simple and obvious, once again a big thank you.

    regards
    Steve
Sign In or Register to comment.