I am trying to allow my users to search a list of record for records that contain a particular medical diagnosis. The Text field in question can contain a number of diagnosis' but I'll usually only be search for one of them. These are IDX codes so as an example I'd like to be able to search for "E11" by typing that into a control on my form and have it return a record that contains field such as "Z15.12, E11.1, D13.24" From my google/stack searching I know I need to use a LIKE expression surrounded by * but I cant seem to get the formatting right. The expression I have working is
txtDxSearch: [tblQAChargesEntered]![Diagnosis] Like Nz([Forms]![MainMenu]![ManagersMenu]![txtDx],"*")
but of course that's missing the * at the front and back and only returns records with the exact value. Can anyone help me with the correct formatting. Just adding the * and trying a variety of single and double quotes hasn't worked and I haven't been able to find a google/stack answer on this topic.
Concatenate wild cards before and after your search text. Here's an example from the Immediate window:
? "Z15.12, E11.1, D13.24" Like "*" & "E11" & "*"
True
However, that pattern will also match "E11" plus another digit:
? "E112" Like "*" & "E11" & "*"
True
If you don't want such matches, revise the pattern to exclude them:
? "E112" Like "*" & "E11" & "[!0-9]*"
False
In your query, try it like this ...
tblQAChargesEntered.Diagnosis Like '*' & Forms!MainMenu!ManagersMenutxtDx & '[!0-9]*'
Also, that Like condition will exclude rows where tblQAChargesEntered.Diagnosis is Null. If you do want them included in your query result set, ask for them explicitly:
tblQAChargesEntered.Diagnosis Like '*' & Forms!MainMenu!ManagersMenutxtDx & '[!0-9]*'
OR tblQAChargesEntered.Diagnosis Is Null