How To…Search Between a Date and 6 Months Prior

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;

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;