Solved

Date query issue based on parameters

Posted on 2013-05-31
6
371 Views
Last Modified: 2013-06-01
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:

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;

Open in new window



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.
0
Comment
Question by:gxs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39211252
I see in the query the parameters have a : infront of them.  Do you need to use the : when you assign the value?

mlmcc
0
 
LVL 1

Author Comment

by:gxs
ID: 39211374
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.
0
 
LVL 27

Accepted Solution

by:
Sinisa Vuk earned 500 total points
ID: 39211572
try to use thus notation:
...
'FROM Statistics WHERE TransDate BETWEEN :pFromDate AND :pToDate';
MainForm.Query.Parameters.ParamByName('pFromDate').Value := DateOf(FromDateEdit.Date);
  MainForm.Query.Parameters.ParamByName('pToDate').Value := DateOf(ToDateEdit.Date); //DateUtils
  MainForm.Query.Open;

Open in new window


..and on Query -> Parameters - set DataType as DateTime for each parameters.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:gxs
ID: 39211781
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:

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;

Open in new window


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.
0
 
LVL 6

Expert Comment

by:House_of_Dexter
ID: 39211857
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...
0
 
LVL 27

Expert Comment

by:Sinisa Vuk
ID: 39211861
I always use English non-separated words for field names like UserName, DateOfRun, ...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Making delphi communicate with a c# service 16 124
Magic Software info 18 144
Firemonkey DbLookupComboBox equivalent ? 2 68
Drag & Drop... Data from one grid to another 2 47
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question