Source language: Translate to:

Force a query search

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Force a query search

Postby carlos torres » Mon May 06, 2013 1:15 pm

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
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Re: Force a query search

Postby Gaev » Mon May 06, 2013 4:35 pm

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]"
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Force a query search

Postby carlos torres » Mon May 06, 2013 9:44 pm

Dear Gaev
Thanks for your quick answer. I will try it!
Regards,
Carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Re: Force a query search

Postby carlos torres » Thu May 09, 2013 2:52 pm

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
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 3 guests