Filter for searching a record in a recordset

Question

I have a VBA code in the field on the form's Header to search for a record (see below). In the table tblPatient where records are searched through there is a field CaseCloseDate. How can I add a condition so that the search would only display records where CaseCloseDate is Null? I have unsuccessfully tried: 

rs.FindFirst "[PatientID] = " & Me.cboMoveTo & " AND [CaseCloseDate] IsNull

-------------------------------------
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

   If Not IsNull(Me.cboMoveTo) Then
       'Save before move.
       If Me.Dirty Then
         Me.Dirty = False
       End If
       'Search in the clone set.
       Set rs = Me.RecordsetClone
       rs.FindFirst "[PatientID] = " & Me.cboMoveTo
       If rs.NoMatch Then
         MsgBox "The Patient File for this patient is not created yet."
       Else
         'Display the found record in the form.
         Me.Bookmark = rs.Bookmark
       End If
       Set rs = Nothing
   End If
End Sub

Answer 1

You need a space between Is and Null, and a double quote at the end:

rs.FindFirst "[PatientID] = " & Me.cboMoveTo & " AND [CaseCloseDate] Is Null"

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Tried as advised: rs.FindFirst "[PatientID] = " & Me.cboMoveTo & " AND [CaseCloseDate] Is Null"

Unfortunately, it does not work. In this dropdown I still see all records even those where CaseCloseDate is Null

Am I doing something wrong?

Answer 3

sorry, is not null

Answer 4

Your code only moves to the first record matching the condition. If you want to filter the form, use

Private Sub cboMoveTo_AfterUpdate()
    If Not IsNull(Me.cboMoveTo) Then
       Me.Filter = "[PatientID] = " & Me.cboMoveTo & " AND [CaseCloseDate] Is Null"
       Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 5

Unfortunately, it does not work. In this dropdown I still see all records even those where CaseCloseDate is Null

sorry, is not null

Change the unbound combo box's RowSource property so that it excludes those rows where the CaseCloseDate column is not Null, e.g.

SELECT PatientID, LastName & ", " & FirstName
FROM Patients
WHERE CaseCloseDate IS NULL
ORDER BY LastName, FirstName;

Set the control's other properties as follows:

BoundColumn:   1
ColumnCount:   2
ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

However, this is not a reliable way of selecting a patient as personal names can legitimately be duplicated.  I was once present at a clinic when two patients arrived within minutes of each other, both female, both with exactly the same first and last names and both with the same data of birth.  Consequently the combo box's RowSource should return other columns which allow such patients to be differentiated.  You'll find an example of a control which does this in NotInList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the RowSource of the combo box in the opening form returns the person's address data in addition to their names.  It does this in a computed column which calls the ConcatValues function from the basConcat module.

---

Ken Sheridan, Stafford, England

Answer 6

PS:  If a value of the CaseClose date might then be entered you'll also need to requery the unbound combo box in the form's AfterUpdate event procedure to remove the patient from the list. 

---

Ken Sheridan, Stafford, England

Answer 7

Thanks a lot for all your help. Fixed it.