Search for value in Two controls-Search Form

[follower2020]

Hello everyone ..

I have a search form that will filter data shown in the Subform.

I am dealing with Patients Data which I save in a table. For Diagnosis Category , I am having 2 fields:

I use the second field for some cases like cancer in the bones ( so for example , I will enter ortho in diagnosis 1 , Cancer in diagnosis 2 )

in the search form , when I select cancer , I want to see the results for both diag 1 and diag 2

suppose I have 10 patients with cancer as diag 1

and 2 patients with cancer as diag 2


here, I want the results to be 10+2 = 12 Patients


I done know how to play with this part of code :

strWhere = strWhere & " AND " & "Patients.DiagnosisCategory1 Like '" & Me.DiagnosisCategory & "'"

so that it shows the required results.

Thank you..

 

[Uncle Gizmo]

I think my Nifty Search Form would be able to do this. Have a look at it on my website here:-

Nifty Search Form - Nifty Access

Nifty Search Form This Advanced but simple to use Search Form is unbelievably easy to set-up. Just create a query and a subform. Following the comprehensive Video Instructions, which demonstrate how to add this incredible utility to your database. Establish yourself as the "Go To" person in your...

www.niftyaccess.com

If you think it's suitable and would like a copy, let me know and I will explain how you can get a free copy.

 

[arnelgp]

strWhere = "(Patients.DiagnosisCategory1 & Patients.DiagnosisCategory2) Like '*" & Me.DiagnosisCategory & "*'"

 

[Gasman]

I read it as Cancer in either of the Diagnosis fields?, so I would use an OR

Part of your problem is your DB is not normalized. If you get a Diagnosis3 field, you will have to change the query.
If it was normalized, you would not.

 

[follower2020]

Thanks to both of you.

I tried this :

strWhere = "(Patients.DiagnosisCategory1 OR Patients.DiagnosisCategory2) Like '*" & Me.DiagnosisCategory & "*'"


but its not giving any results !

( it will be caner only in one of the 2 fields, the other could be Ent , ortho ,etc. )

 

[Gasman]

Try along the lines of

strWhere =  "(Patients.DiagnosisCategory1 Like '*" &  Me.DiagnosisCategory & "*')  OR  (Patients.DiagnosisCategory2 Like '*" &  Me.DiagnosisCategory & "*')"

or use the QBE GUI to get the syntax correct, then amend for variable.

 

[follower2020]

Dear

even that didnt work for me !

but when I used these 2 lines together it work perfect ONLY if all other fields in the search form are left blank ( fields like age , or Hospital name etc ):

strWhere = "Patients.DiagnosisCategory1 Like '" & Me.DiagnosisCategory & "'"

strWhere = strWhere & " or " & "Patients.DiagnosisCategory2 Like '" & Me.DiagnosisCategory & "'"


What am I missing here ?

Thank you.

 

[arnelgp]

strWhere = "(Patients.DiagnosisCategory1 OR Patients.DiagnosisCategory2) Like '*" & Me.DiagnosisCategory & "*'"

this is different from what i posted in post #3.
i did not use OR, i use &.

 

[follower2020]

this is different from what i posted in post #3.
i did not use OR, i use &.

you are right. I have tried yours first but it jut give wrong results like some patients under rhematology.

don't know why .

note that diagnosiscategory2 could be blank is some cases : only diagnosiscategory1 must not be blank

 

[arnelgp]

did you understand what i just said.
i already tested this.

you have:

strWhere = "(Patients.DiagnosisCategory1 OR Patients.DiagnosisCategory2) Like '*" & Me.DiagnosisCategory & "*'"

while I have:

strWhere = "(Patients.DiagnosisCategory1 & Patients.DiagnosisCategory2) Like '*" & Me.DiagnosisCategory & "*'"

 

[follower2020]

did you understand what i just said.
i already tested this.

you have:

strWhere = "(Patients.DiagnosisCategory1 OR Patients.DiagnosisCategory2) Like '*" & Me.DiagnosisCategory & "*'"

while I have:

strWhere = "(Patients.DiagnosisCategory1 & Patients.DiagnosisCategory2) Like '*" & Me.DiagnosisCategory & "*'"

I have tried that . its showing some cases that are not related Plus cancer cases . for example , there are many cases with diagnosiscategory1 as "Dental" ( and of course those are with blank diagnosiscategory2.

 

[arnelgp]

here is a demo.