gxs
asked on
Date query issue based on parameters
Hi fellas,
It's been a long time, happy to be here again.
I have a little issue with retrieving info based on a date range:
The above code works flawlessly when a basic query is written such as "Select * from"
But when it came to this complicated/boring query, the following error popped up:
No value given for one or more required parameters.
I don't know why Access is not detecting the parameters even though I checked the property ParamCheck in the used TADOQuery.
I doubled checked the columns names also and they are totally fine.
Any ideas?
Appreciated in advance,
Phil.
It's been a long time, happy to be here again.
I have a little issue with retrieving info based on a date range:
procedure TReportsForm.FilterBtnClick(Sender: TObject);
begin
MainForm.Query.Close; Elder
MainForm.Query.SQL.Clear;
MainForm.Query.SQL.Text := 'SELECT sum(AdultMale), sum(AdultFemale), sum(ChildMale), sum(ChildFemale), sum(ElderMale), sum(ElderFemale), ' +
'sum(PermEx), sum(DecEx), sum(SurgEx), sum(TempFill), sum(AmalFill), sum(CompFill), sum(InstRCT), ' +
'sum(DecRCT), sum(PermRCT), sum(PerPerio), sum(ScallPerio), sum(LesionPerio), sum(OLAMS), ' +
'sum(OGAMS), sum(DresMS), sum(PARay), sum(OcclRay), sum(ExoRay), sum(FullSet), sum(PartialSet), ' +
'sum(SetRepair), sum(PMSet), sum(FixedCrown), sum(FixedBridge), sum(OHI), sum(FS), sum(FSO) ' +
'FROM Statistics WHERE TransDate BETWEEN :FromDate AND :ToDate';
MainForm.Query.Parameters.ParamByName('FromDate').Value := DateOf(FromDateEdit.Date);
MainForm.Query.Parameters.ParamByName('ToDate').Value := DateOf(ToDateEdit.Date); //DateUtils
MainForm.Query.Open;
MainForm.frxReport.ShowReport();
Close;
end;
The above code works flawlessly when a basic query is written such as "Select * from"
But when it came to this complicated/boring query, the following error popped up:
No value given for one or more required parameters.
I don't know why Access is not detecting the parameters even though I checked the property ParamCheck in the used TADOQuery.
I doubled checked the columns names also and they are totally fine.
Any ideas?
Appreciated in advance,
Phil.
ASKER
Thanks for your participation mlmcc.
The colon is used or called a bind variable in other words.
It gives the query the ability to use different values which is the case of selecting different dates through the datetimepicker.
Hope it is clear to you.
The colon is used or called a bind variable in other words.
It gives the query the ability to use different values which is the case of selecting different dates through the datetimepicker.
Hope it is clear to you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sinisav, your suggestion didn't work but it doesn't mean that it is wrong. :p
I mean damn it man sometimes little things do drive you crazy. :@
The fix was the following:
The code in my older comment is an old one. The above one has a lot of dashes. By restricting them into square brackets, the problem is solved.
It is the same thing that I discussed with you and the other guys in my other subject when I said square brackets do solve many Access compatibility issues such as queries that include Arabic or Chinese Alphabet. But to be honest, I have never imagined that Access would be fooled by a dash :O, :@.
Appreciated.
I mean damn it man sometimes little things do drive you crazy. :@
The fix was the following:
procedure TReportsForm.FilterBtnClick(Sender: TObject);
begin
MainForm.Query.Close; Elder
MainForm.Query.SQL.Clear; //[Child-Male]
MainForm.Query.SQL.Text := 'SELECT sum(AdultMale), sum(AdultFemale), sum([Child-Male]), sum(ChildFemale), sum(ElderMale), sum(ElderFemale), ' +
'sum(PermEx), sum(DecEx), sum(SurgEx), sum(TempFill), sum(AmalFill), sum(CompFill), sum(InstRCT), ' +
'sum(DecRCT), sum(PermRCT), sum(PerPerio), sum(ScallPerio), sum(LesionPerio), sum(OLAMS), ' +
'sum(OGAMS), sum(DresMS), sum(PARay), sum(OcclRay), sum(ExoRay), sum(FullSet), sum(PartialSet), ' +
'sum(SetRepair), sum(PMSet), sum(FixedCrown), sum(FixedBridge), sum(OHI), sum(FS), sum(FSO) ' +
'FROM Statistics WHERE TransDate BETWEEN :FromDate AND :ToDate';
MainForm.Query.Parameters.ParamByName('FromDate').Value := DateOf(FromDateEdit.Date);
MainForm.Query.Parameters.ParamByName('ToDate').Value := DateOf(ToDateEdit.Date); //DateUtils
MainForm.Query.Open;
MainForm.frxReport.ShowReport();
Close;
end;
The code in my older comment is an old one. The above one has a lot of dashes. By restricting them into square brackets, the problem is solved.
It is the same thing that I discussed with you and the other guys in my other subject when I said square brackets do solve many Access compatibility issues such as queries that include Arabic or Chinese Alphabet. But to be honest, I have never imagined that Access would be fooled by a dash :O, :@.
Appreciated.
To debug problems like this...put the actual SQL into the TQuery component...
instead of parameters...put the actual values in the query...
set the query to active in the Object Inspector...see if you get any errors...then replace one of the parameters...and repeat...adding the parameters until you find the offending problem...this way you can find if its a problem with the SQL or the parameters...
instead of parameters...put the actual values in the query...
set the query to active in the Object Inspector...see if you get any errors...then replace one of the parameters...and repeat...adding the parameters until you find the offending problem...this way you can find if its a problem with the SQL or the parameters...
I always use English non-separated words for field names like UserName, DateOfRun, ...
mlmcc