help with query criteria with multiple chk boxes

[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.

 

[Jeff Boyce]

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

 

[mhmaid]

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

 

[John Spencer]

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
'====================================================

 

[Jeff Boyce]

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

 

[mhmaid]

thats right . one procedure for each patient.

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