Search for value in Two controls-Search Form (1 Viewer)

follower2020

New member
Local time
Today, 23:47
Joined
May 14, 2020
Messages
9
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

Nifty Access Guy
Staff member
Local time
Today, 20:47
Joined
Jul 9, 2003
Messages
16,245
I think my Nifty Search Form would be able to do this. Have a look at it on my website here:-


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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
strWhere = "(Patients.DiagnosisCategory1 & Patients.DiagnosisCategory2) Like '*" & Me.DiagnosisCategory & "*'"
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
14,047
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

New member
Local time
Today, 23:47
Joined
May 14, 2020
Messages
9
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

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
14,047
Try along the lines of
Code:
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

New member
Local time
Today, 23:47
Joined
May 14, 2020
Messages
9
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
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

New member
Local time
Today, 23:47
Joined
May 14, 2020
Messages
9
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
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

New member
Local time
Today, 23:47
Joined
May 14, 2020
Messages
9
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
here is a demo.
 

Attachments

  • diagnose.accdb
    460 KB · Views: 467

Users who are viewing this thread

Top Bottom