select field which contains certain text

[Guest]

Hi,
I have a query which looks into a table called ROS/PMH_Diagnosis for data in
a field called Diagnosis. I want to return the text if it contains the
desired value. it is also filtered by social security number so only the
diagnosis for a particular patient will be returned. i use this DLookup
function:

Diabetes: DLookUp("[Diagnosis]","ROS/PMH_Diagnosis","[SSN]=" & "'" & [SSN] &
"'" & " AND [Diagnosis]=" & "'Diabetes mellitus Insulin dependant'" & "")

I want to work diabetes to come back if the diagnosis is Diabetes. Here is
my problem, i have several different types of diabetes diagnosis, ie.
Diabetes mellitus Insulin dependant, diabetes mellitus non-insulin dependant,
diabetes mellitus with osteomyleteis, etc etc etc.

how do i change this lookup to find all that contain "Diabetes mellitus"?
thank you very much
russ

 

[Guest]

Try LIKE instead of '=' in the AND part of the clause. Then use (probably)
"%" to match anything following 'Diabetes mellitus' as in 'Diabetes
mellitus%'. The syntax is accepted just as it would be in an SQL WHERE clause.

Diabetes: DLookUp("[Diagnosis]","ROS/PMH_Diagnosis","[SSN]=" & "'" & [SSN] &
"'" & " AND [Diagnosis] LIKE " & "'Diabetes mellitus Insulin dependant'" &
"")

 

[Guest]

Sorry, that should have read "(probably) "*" not '%'- I'm getting my
languages confused.