Question

“How can I search on a specified date and 6 months prior?”

Solution

Use the Query Designer to build a query that contains two search criteria on the Date. Use the <= and >= operators. Designate the first criteria as AutoSearch and use SQLBuilder to modify the second criteria at runtime.

Download: SQLBuilderSearchOnDateMinus6Months.zip

Sample RAP code:

procedure ReportBeforeOpenDataPipelines;
var
  lSQLBuilder: TdaSQLBuilder;
  ldDate: TDate;
  liYear: Integer;
  liMonth: Integer;
  liDay: Integer;
begin

  lSQLBuilder := TdaSQLBuilder.Create(orders);

  {get date entered by user}
  ldDate := Report.AutoSearchFields[0].Value;
  
  {decode the date}
  DecodeDate(ldDate, liYear, liMonth, liDay);
  
  {adjust date to 6 months prior}
  liMonth := liMonth - 6;
  
  if liMonth < 0 then
    begin
      liYear := liYear - 1;
      liMonth := 12 + liMonth;
    end;
    
  ldDate := EncodeDate(liYear, liMonth, liDay);
  
  {modify search criteria value}
  lSQLBuilder.SearchCriteria[1].Value := DateToStr(ldDate);

  lSQLBuilder.ApplyUpdates;
  lSQLBuilder.Free;


end;