help with query criteria with multiple chk boxes

  • Thread starter Thread starter mhmaid
  • Start date Start date
M

mhmaid

I have a search form with one unbound field named , a button to run a query
with the following sql
SELECT *
FROM Patients
WHERE patient Like "*" & Forms!search!patient & "*" or country Like "*" &
Forms!search!patient & "*" or Diagnosisdetails Like "*" &
Forms!search!patient & "*" or Remarks Like "*" & Forms!search!patient & "*"
or Approvedby Like "*" & Forms!search!patient & "*" or Cpr Like "*" &
Forms!search!patient & "*" or Diagnosis Like "*" & Forms!search!patient & "*"
or Hospital Like "*" & Forms!search!patient & "*" or Procedure Like "*" &
Forms!search!patient & "*";

now , i want to add check boxes for each control like one chk box for
country , one for patienname ,etc,
the reason is , i want the query to search in a field only if the chk box of
that field is "yes"
offcourse i dont have these chk boxes in my table.this is only to reduce
the result of the query ( i want to get the result only for chk boxes ticked)
, may also need another chk box like "all"

searched the group but could not get any thing that worked.
how the sql should be in this case.
 
It all starts with the data ... and I don't have a very clear picture how
your data is structured.

Please post a description of your tables/fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
thanks for reply

in my table i have many fields
patient=patientname
country
diagnosis
diagnosisdetails
procedure
approvedby
remarks
any many other fields

and i have created a query with the above sql
my aim is to make a search form like this
one unbound field to enter search word
a button to run the query
nine chk boxes ( unbound) i.e. the search form itself is unbound
so, when i enter fo example the word london , i may want the query to search
only in the field counrtry, but sometimes i may want also to search in the
field "remarks"

same thing for other fields , some times i want to search in the field
"patient" plus the field "remarks" as i have important notes in the last one.

so the fiels that i have are those as in the sql, no fields are there in my
table for chk box , chk boxes here only to reduce the results.

thanks
 
The following MAY work or you may get a query too complex error. Note
the inclusion of the parentheses to pair up the criteria.

SELECT *
FROM Patients
WHERE (patient Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckPatient = True)

or (country Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckCountry = True)

or (Diagnosisdetails Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckDXDetail = True)

or (Remarks Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckRemarks = True)

or (Approvedby Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckApproved = True)

or (Cpr Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckCPR = True)

or (Diagnosis Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckDx = True)

or (Hospital Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckHospital = True)

or (Procedure Like "*" & Forms!search!patient & "*"
AND Forms!Search!CheckProcedure = True)

You might take a look at the following URL for some ideas on a better
way to manage this.

http://allenbrowne.com/ser-62.html

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Based on your description of the table, it looks like a patient can have
only one [diagnosis] (and only one [procedure]). Is that
accurate/intentional?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
thats right . one procedure for each patient.

Hi john .
I have tested the sql and it worked with me.thanks for help
 
Back
Top