Page 1 of 1

Force a query search

PostPosted: Mon May 06, 2013 1:15 pm
by carlos torres
Hi everybody

How can force an msacces' query to just a few o records?
I'm working Ondontological records (histories) now I have a new requirement the 'treatment plan'. I add a new treatment to a pacient, assuming he already has two treatments how force the query to show me just the two results that correspond to that patient not the whole records in it. The common key field is the id of the patient. This is the code I'm using
Code: Select all
dbpOpenTable "Dentist" "Plan" ""
... dbpQuery "Dentist" "Plan" "IdPacPlan= [Dentist.HCO.IdPaciente]"
dbpExecSQL "Dentist" "SELECT Plan.IdPacPlan, Plan.Tooth, Plan.Code, Plan.Detail, Plan.Value, Plan.payment, Plan.date|FROM HCO INNER JOIN Plan ON HCO.IdPaciente = Plan.[IdPacPlan]|WHERE (((Plan.IdPacPlan)=[#34][Dentist.HCO.IdPaciente][#34]))|ORDER BY Plan.Date;|" ""
dbpShowGrid "Dentist" "Plan" "rPlan"
...///

I tried with both statement dbpQuery and dbpExecSQL and none of them worked.
I don't open the table before the query.

I'll appreciate your help
regards

carlost

Re: Force a query search

PostPosted: Mon May 06, 2013 4:35 pm
by Gaev
carlost:

Instead of referencing [Dentist.HCO.IdPaciente] within the dbpQuery/dbpExecSQL commands ... try ...

SetVar "[thisIdPaciente]" "[Dentist.HCO.IdPaciente]"

... and then do something like ...
Code: Select all
dbpQuery "Dentist" "Plan" "IdPacPlan=[thisIdPaciente]"


Note that if the IdPacPlan field is not numeric, you have to surround the value with 'double quotes' like ...
Code: Select all
dbpQuery "Dentist" "Plan" "IdPacPlan=[#34][thisIdPaciente][#34]"

Re: Force a query search

PostPosted: Mon May 06, 2013 9:44 pm
by carlos torres
Dear Gaev
Thanks for your quick answer. I will try it!
Regards,
Carlost

Re: Force a query search

PostPosted: Thu May 09, 2013 2:52 pm
by carlos torres
Thanks to everyone
Finally I solved the problem... I had to work around for a while but...I got it.
I create a view from the original Plan and worked on it.

Code: Select all
dbpOpenTable "Dentist" "Plan" ""
dbpCreateView "Dentist" "Plan" "PlanView"
dbpOpenTable "Dentist" "PlanView" ""
dbpExecSQL "Dentist" "SELECT * FROM PlanView|WHERE (((PlanView.IdPacPlan)=[#34][Dentist.HCO.IdPacient][#34]))|ORDER BY PlanView.Fecha;|" ""
dbpShowGrid "Dentist" "PlanView" "rPlan"


I didn't know exactly why or how it works but it works!
I need to study a bit more.

Regards
carlost