Filtering two IBTable and insert or edit IBTable

[danten]

OK, ERROR:
"Unsupported Feature"

Delphi-Quellcode:

Query.Params[0].Name := 'start';
Query.Params[0].DataType := ftTimeStamp;
Query.Params[0].ParamType := ptUnknown;
Query.Params[0].Value := EncodeDate(2014, 2 ,17);
Query.Params[1].Name := 'end';
Query.Params[1].DataType := ftTimeStamp;
Query.Params[1].ParamType := ptUnknown;
Query.Params[1].Value := EncodeDate(2014, 2 ,20);

[DeddyH]

Did you try my (much shorter) code? What is the datatype of "WHEN"?

[mkinzler]

I would change the name when to a not reserved name. The paramters should be created automatically, when ParamCheck is true.

[danten]

Zitat:

Zitat von DeddyH (Beitrag 1250289)

Did you try my (much shorter) code? What is the datatype of "WHEN"?

"WHEN" =>> ftDateTime

Delphi-Quellcode:

SELECT 
  * 
FROM
  ATTENDANT 
WHERE
  "WHEN" BETWEEN :start1 AND :end1

New Error:
"Dynamic SQL Error.
SQL error code=-206
Column unknown
START1
At line 1, column 48."

[sx2008]

Zitat:

Zitat von mkinzler (Beitrag 1250295)

I would change the name when to a not reserved name.

WHEN is a reserved word.
Using reserved words in SQL could lead to VERY STRANGE errors.
I (and mkinzler) recommend to change the name of the field.

PS: don't set ParamType to ptUnknown! Set it to ptInput.

[danten]

:-D Thank you to all friends 8-)

Delphi-Quellcode:

function Tfrm_main.Attendant_Filter:boolean;
var
  myYear,myMonth,myDay:word;
  set_day, my_day,
  set_week,start_week,end_week,
  set_month,start_month,end_month,
  day_month,
  set_year, start_year,end_year:string;
begin

  DecodeDate(NOW, myYear, myMonth, myDay);

  ///////////////////////////////////////////////////////////

    (* Filter day *)
  my_day := DateToStr(NOW);
  set_day := '("WHEN" >= ' + QuotedStr(my_day) + ')';

  (* Filter week *)
  start_week := IntToStr(dayofweek(now -1))+'.'+ IntToStr(myMonth)+'.'+IntToStr(myYear);
  end_week := IntToStr(dayofweek(now -1)+7)+'.'+ IntToStr(myMonth)+'.'+IntToStr(myYear);

  set_week := '("WHEN" >= ' + QuotedStr(start_week)
              + ') and ("WHEN" <= ' + QuotedStr(end_week)
              + ')';

  (* Filter  month *)
  start_month := '1.'+ IntToStr(myMonth)+ '.' + IntToStr(myYear);
//  day_month := IntToStr(MonthDays[IsLeapYear(myYear)][2]); //
  end_month :=  '1.' + IntToStr(myMonth + 1) + '.' + IntToStr(myYear);

  set_month := '("WHEN" >= ' + QuotedStr(start_month)
              + ') and ("WHEN" <= ' + QuotedStr(end_month)
              + ')';

  (* Filter year*)
  start_year := '1.1.'+ IntToStr(myYear);
  end_year := '1.1.'+ IntToStr(myYear +1);

  set_year := '("WHEN" >= ' + QuotedStr(start_year)
              + ') and ("WHEN" <= ' + QuotedStr(end_year)
              + ')';

  ///////////////////////////////////////////////////////////////

  ShowMessage(QuotedStr(start_year) + ' : '+ QuotedStr(end_year));
  ShowMessage(QuotedStr(start_month) + ' : '+ QuotedStr(end_month));
  ShowMessage(QuotedStr(start_week) + ' : '+ QuotedStr(end_week));
  ShowMessage(QuotedStr(my_day)); 
  ........
  Result := True;

end;

[DeddyH]

Now set ShotDateFormat to e.g. "MM/DD/YYYY" and try it again.

[danten]

Thank you perfect. :)

Last question: How do I know the days last week.?
start_old_week := ????? return = 24.2.2014 ?????
end_old_week := ????? return = 2.3.2014 ?????

[DeddyH]

I don' t know what you mean exactly :gruebel:. You might have a look at the "DateUtils"-Unit, maybe there' s a function for your purpose.

[danten]

Find out last week, when it started and when it ended. Return date.