How to find a record with an empty field value using VBA in Access?

Question

I'm a bit confused on how exactly VBA views empty field values in a table/datasheet because I'm trying to use Recordset.FindFirst method to locate a record within my datasheet, but if the record contains a field that is empty, it cannot locate the record.

For example, here's what my table may look like:

FieldOne   FieldTwo
123        SomeName1
456        
789        SomeName2

For record 456, the value in FieldTwo is empty. The datatype for FieldOne and FieldTwo is a Short Text.

Assume myDatasheet is linked to the above table with those mentioned records.

I attempt to find the record 123 like so:

Dim crit As String
crit = "FieldOne = '" & Me.textBoxOne & "' And " & _
       "FielTwo = '" & Me.textBoxTwo & "'"

With myDatasheet.Form
    .RecordsetClone.FindFirst crit 

    If Not .RecordsetClone.NoMatch Then
        MsgBox "FOUND"
    Else
        MsgBox "NOT FOUND"
    End If
End With

Where the value in Me.textBoxOne is 123 and in Me.textBoxTwo is SomeName1 and I get a FOUND.

However, if I try to find record 456 using Me.textBoxOne as 456 and Me.textBoxTwo as "", I get a NOT FOUND.

There's got to be something syntactically wrong with my code. What should the correct criteria be?

Thanks.

Try building your criteria with BuildCriteria: stackoverflow.com/a/224961/3820271 or stackoverflow.com/a/49198954/3820271 — Andre
Would it be an option to use the Nz function? It turns Null into empty Strings by default and can be used in SQL statements as well, so I guess it would also work with the above criteria. E.g. "Nz(FieldTwo) = '" & Nz(Me.textBoxTwo) & "'", then the Field value as well as the Textbox value could be either empty strings or Null. — Jörg Brenninkmeyer

Erik A Erik A · Accepted Answer · 2018-04-25T07:01:39

Null is not the same as an empty string. Your "empty" field likely is Null, and comparing Null to any string resuts in Null, which gets parsed as False.

While you can add some logic to check if your form field is null or an empty string, and then use Is Null in the condition, like KostasK suggests, there's a simple workaround: just concatenate an zero-length string to your field. That will cause Null values to become zero-length strings, making them pass the comparison:

crit = "FieldOne = '" & Me.textBoxOne & "' And " & _
       "FielTwo & '' = '" & Me.textBoxTwo & "'"

Note that this will invalidate any indexes, which might lead to performance issues in very select situations.

How to find a record with an empty field value using VBA in Access?

1 Answers