select statement using variable as comparison

rbuser

I am attempting to use a select statement that compares a value against a
variable I declared earlier in the report. When I run the report I get an
error 'column unknown'.
q.SQL.Text := 'Select SUM(AMOUNT)AMT from AR_DETAIL ' +

'where tran_date>myvariable.asdate and TRAN_DATE<="11/15/2001" AND
ARA_AUTO_KEY = ' + PLAR_ACCOUNT['ARA AUTO KEY'];

Any ideas?



Thanks,

Aaron

nardmoseley

Sorry, but do not understand what are trying to accomplish.

In general, you should not manipulate any of the datasets while the
report is executing.

In your code below, when you build the 'where' string the
myVariable.AsDate reference is included in the quotes. It should be
coded more like this

{format the date}
lsDate := FormatDateTime('MM/DD/YYY', myVariable.AsDate);

{put the date in the string}
'where tran_date > ' + lsDate + ' and TRAN_DATE<="11/15/2001" AND
ARA_AUTO_KEY = ' + PLAR_ACCOUNT['ARA AUTO KEY'];


A SQL Text string is a string of text that is submitted to a database
server

rbuser

Thanks for the advice and sorry about not being more clear. What I am
attempting to accomplish is to sum a field within a given date range. The
date upper and lower date range will be equal to 2 variables that I created
in the title of the report. For simplicity, the variables are VA.asdate and
VB.asdate. Below is an example of how I declard the two variables:

begin

VA.ASDATE:=11/15/2001;

end;

Now I did as you suggested below but I got an undeclard indentifier error so
I added

lsDate: string;

I also changed the formatdatetime('MM/DD/YYY by added a fourth Y; . The code
was as follows:

var

q: TQuery;

lsdate: string;



begin

V1TO30.ASDOUBLE:=0;

lsDate:=FormatDateTime('MM/DD/YYYY',VA.asdate);

q := TQuery.Create(nil);

q.DatabaseName := 'DBQQDATA';


q.SQL.Text := 'Select SUM(AMOUNT) AMT from AR_DETAIL ARD ' +

'where ARD.TRAN_DATE<= '+ lsdate +' AND ARD.ARA_AUTO_KEY = ' +
PLAR_ACCOUNT['ARA AUTO KEY'];

q.Open;


V1TO30.ASDOUBLE := q.FieldByName['AMT'].AsFLOAT;

DETAIL.VISIBLE:=(V1TO30.ASDOUBLE<>0);

q.Free;


end;

Now when I go to run the report I get the following error message:

Invalid Modify request, conversion error from string "a bunch of numbers'.

Any help would be greatly appreciated.

Thanks,

Aaron