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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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; |