Unexpected behaviour with .FindFirst VBA MS Access function: .NoMatch always returns true

Question

View the following lines of code below.

Dim rst As DAO.Recordset
Dim strSql As String

strSql = "SELECT * FROM MachineSettingsT;"
Set rst = DBEngine(0)(0).OpenRecordset(strSql)

rst.FindFirst "Microwave = " & "'" & Me.Microwave & "'" & " AND WashingMachine =" & "'" & Me.WashingMachine & "'" & " AND Element1 =" & "'" & Me.Element1 & "'" _
               & "AND Element3 =" & "'" & Me.Element3 & "'" & "AND Dryer =" & "'" & Me.Dryer & "'" & "AND SettingID <>" & "'" & Me.SettingID & "'"

If Not rst.NoMatch Then  
    Cancel = True
    If MsgBox("Setting already exists; go to existing record?", vbYesNo) = vbYes Then
        Me.Undo
        DoCmd.SearchForRecord , , acFirst, "[SettingID] = " & rst("SettingID")
    End If
End If
rst.Close

Problem: If any of the values in the rst.FindFirst expression are Null then rst.NoMatch always returns true even when there is a record with a matching Null value in the field being evaluated. Is this behaviour to be expected or could there be another underlying problem. I checked the msdn page but it did not provide information about this sort of behaviour.

Fionnuala · Accepted Answer · 2013-02-06T15:43:21.970

2

Consider a different approach. Note that this is for a set of text data type fields.

Dim rst As DAO.Recordset
Dim strSql As String
Dim db As Database

Set db=CurrentDB

strSql = "SELECT * FROM MachineSettingsT WHERE 1=1 "
''Set rst = db.OpenRecordset(strSql)

If not IsNull(Me.Microwave) Then
   strWhere =  " AND Microwave = '" & Me.Microwave & "'" 
End if
If not IsNull(Me.WashingMachine) Then
   strWhere = strWhere & " AND WashingMachine ='" & Me.WashingMachine & "'"
End if
If not IsNull(Me.Element1) Then
   strWhere = strWhere & " AND Element1 ='" & Me.Element1 & "'" 
End if
If not IsNull(Me.Element3) Then
   strWhere = strWhere & " AND Element3 ='" & Me.Element3 & "'"  
End if
If not IsNull(Me.Dryer) Then
   strWhere = strWhere & " AND Dryer ='" & Me.Dryer & "'"
End if

Set rst = db.OpenRecordset(strSql & strWhere)

edited Feb 06 '13 at 15:43

answered Feb 06 '13 at 14:50

Fionnuala

90,370
7
114
152

1

+1 Nice use of the `Where 1=1` to ensure you don't need to worry about whether to use `Where` or `And` based on the values being null. – Matt Donnan Feb 06 '13 at 16:08
Thank you very much. Works like a charm. Note though that a clause needs to be added in the if statement so that the Null conditions are still evaluated. e.g. If NOT IsNull(Me.Microwave) Then strWhere = strWhere & ... Else strWhere = " AND Microwave IS NULL" End If – jaromey Feb 06 '13 at 17:34
More info on the Where 1=1 [here](http://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause) – jaromey Feb 06 '13 at 17:49

HansUp · Answer 2 · 2015-11-03T04:26:17.187

When a control value is Null, your .FindFirst criteria must check whether the corresponding field Is Null instead of equal to the control's value. Start with a simpler example, examining two control/field pairs.

Dim strCriteria As String
If IsNull(Me.Microwave) Then
    strCriteria = " AND Microwave Is Null"
Else
    strCriteria = " AND Microwave = '" & Me.Microwave & "'"
End If
If IsNull(Me.WashingMachine) Then
    strCriteria = strCriteria & " AND WashingMachine Is Null"
Else
    strCriteria = strCriteria & " AND WashingMachine = '" & Me.WashingMachine & "'"
End If
If Len(strCriteria) > 0 Then
    ' discard leading " AND "
    strCriteria = Mid(strCriteria, 6)
    Debug.Print strCriteria 
    rst.FindFirst strCriteria
End If

This method works as well but I used @Remou's method to solve my problem. Thank you and I will now know for future reference to be careful with null values when using .FindFirst — jaromey, Feb 06 '13 at 17:59

Unexpected behaviour with .FindFirst VBA MS Access function: .NoMatch always returns true

2 Answers2