Search Criteria Question using system date

rbuser

D7, RB 10.07

Designing a report and in the Data tab, Query Designer, Search tab.

Say you have a field named TransDate. Is there a way to say the following?
where TransDate>=SystemDate-30

I have tried TransDate>=getdate()-30 and that doesn't work. I don't want to
code it from Delphi. I want the user to have the freedom to change the '30'
to whatever range they want for their permanent use. Also, this table has
so many records that reading all records and then using code to only print
certain ones is not a valid option either as it will be very inefficient.

Thanks
Bob

nicocizik

Hi Bob,

Which database are you using? Does the Query designer actually create the
SQL correctly when viewing it in the SQL tab? You might try getting the
query working first without RB then try to recreate it in the Query
Designer.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Bob,

I use RAP and the OnInitializeParameters() event to init the query
parameters.
Example sets a search condition to the first and last day of the current
month.
You should be able to do something similar.

Regards,
Jeroen R?ttink

procedure ReportOnInitializeParameters(var aCancel: Boolean);
var StartSelectie : Date;
EindSelectie : Date;
Year, Month, Day : integer;
begin
DecodeDate(CurrentDate, Year, Month, Day);
StartSelectie := EncodeDate( Year, Month, 1 );
if Month < 12
then Month := Month + 1
else begin
Year := Year + 1;
Month := 1;
end;
EindSelectie := EncodeDate( Year, Month, 1 ) - 1;

report.autosearchfields[0].SearchExpression := DateToStr(StartSelectie) +
',' +
DateToStr(EindSelectie);
end;

rbuser

Using MS SQL Server. In SQL, the following works. However, I can't get
that to work in RB.
TransDate>=getdate()-30

I was hopeing you have a work around.

Thanks
Bob

rbuser

I will try that but that isn't as end-user friendly. Was thinking the
standard sql syntax should work.

where TransDate>=getdate()-30

Thanks,
Bob

nicocizik

Hi Bob,

The SQL object checks the reserved function list to see if the function you
are defining can be used by calling the daGetFunctionList routine located in
the daSQLReservedWords.pas file. You can see that this list is very generic
and does not include DB specific functions such as GetDate(). We currently
have plans to update this list for each supported database for the next
major release.

One workaround would be to simply add a new entry to the daGetFunctionList
routine containing the GetDate() function. Another would be to override the
Session.ContainsSQLFunctionCall routine in the ADO plugin and add the
function manually.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com