Sql like returns false records

[hmenzel]

SELECT tbl_Visits.vst_VisitDate, tbl_Visits.vst_Complaint, tbl_Visits.vst_Diagnosis
FROM tbl_Visits
WHERE (((tbl_Visits.vst_Complaint) Like "*asthma*")) OR (((tbl_Visits.vst_Diagnosis) Like "*asthma*"));

returns records with no occurrences of asthma. about 4 of 638 hits.
Thanks,
Harold Menzel

 

[plog]

Show me an example. Include all fields of data for that record.

 

[spikepl]

... and include the entire content of each field, not only what is immediately visible on screen (if different).

 

[hmenzel]

see attached SQL was
SELECT tbl_Visits.vst_VisitDate, tbl_Visits.vst_Complaint, tbl_Visits.vst_Diagnosis
FROM tbl_Visits
WHERE (((tbl_Visits.vst_Complaint) Like "*liver*")) OR (((tbl_Visits.vst_Diagnosis) Like "*liver*"));

 

[GinaWhipp]

Try changing the *OR* to *AND*

 

[hmenzel]

AND does not give the correct results .
Records 36 and 44 are in error.
Thanks,
Harold Menzel

 

[spikepl]

you attached an excel file- what does it show? which records are "false"? not 36 nor 44

 

[spikepl]

ah ok, row 37 and 45. But you are showng a spreadsheet that can be confused by linefeeds. Show screenshot of same lines produced by query, and make sure all characters of field are visible,


he he - try to increase row height. then you'll see liver also in those lines

 

[hmenzel]

Sorry, the SQL file had no header line 37 and 45 are bad.
Thanks,
Harold Menzel

 

[hmenzel]

Here it is.

Thanks,
Harold Menzel

 

[plog]

First, "asthma" isn't "liver". The example you posted isn't the same as your initial post.

Second, ever record in your example file contains "liver" in the fields you have tested. The problem is you are picking up the word "deliver".

Third, now you are stuck between a false-positive rock and a false-negative hard spot. There's two ways to rectify this:

1. You can explicitly omit records with "deliver" in them (AND NOT LIKE "*deliver*"). However, you will exclude records that say something like "liver not delivering bile". And you will still pick up records that say "Patients livery was burned to them."

2. You can change your criteria to include spaces around "liver" (LIKE "* liver *"). However that will exclude records where liver is immediately preceded or followed by a special character (e.g. "Ruptured liver.", "Internal bleeding (liver) and laceration.")

 

[plog]

FYI, both 37 and 45 actually contain the word you are searching for. Make the rows larger in your excel file.


37:

"Hepatits
Choledocholithiasis, Liver CA, alcoholic liver disease"


45:

"1. HTN
2. hypothyroidism
3. abnormal liver function
4. Eustacian tube dysfunction"

 

[spikepl]

OP is simply confused by the field containing more than what is visible in the one line displayed.

 

[hmenzel]

Thanks Spikepl makes sense now, I wil have to change the output display and tell the people entering the data not to use line feeds.

Harold Menzel

 

[spikepl]

Instructions for people entering data? Not a reliable method. Poeple do what comes naturally.

Also, your setup will be prone to typos. Consider selecting complaints, diagnoses from lists: a normal setup would be to have a long list and select items from it that turn up in a short list of selected items. With an option to add a new item, if not yet available. The lists could also be narrowed down by categories. This would bypass many potential problems.