Filter for searching a record in a recordset RRS feed

  • 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."
             'Display the found record in the form.
             Me.Bookmark = rs.Bookmark
           End If
           Set rs = Nothing
       End If
    End Sub

    Friday, October 28, 2016 5:14 PM


All replies

  • 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 (

    Friday, October 28, 2016 5:40 PM
  • 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?

    Friday, October 28, 2016 5:45 PM
  • sorry, is not null
    Friday, October 28, 2016 5:46 PM
  • 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
            Me.Filter = ""
            Me.FilterOn = False
        End If
    End Sub

    Regards, Hans Vogelaar (

    Friday, October 28, 2016 8:16 PM
  • 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 in my public databases folder at:!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

    Saturday, October 29, 2016 3:00 PM
  • 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

    Saturday, October 29, 2016 3:13 PM
  • Thanks a lot for all your help. Fixed it.
    • Marked as answer by Monday, October 31, 2016 5:10 PM
    Monday, October 31, 2016 1:09 PM