You are correct.  "" is not null it is a zero-length string.

You can use criteria

Field: Whatever
Criteria: is not Null and <> ""

or change your procedure to

If strFieldX <> "" Then Rec.FieldX = strFieldX

Or if you really want to be sure that Rec.Fieldx is null

IF strFieldX = "" Then
 Rec.FieldX = NULL
ELSE
 Rec.FieldX = strFieldX
END IF

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Yes.  The zero-length string "" is *never* equal to Null.  Null is a "no
data" condition, "" is data that happens to have zero-length.

Instead of

... how about using this:

   If Len(strFieldX) > 0 Then
       Rec.FieldX = strFieldX
   Else
       Rec.FieldX = Null
   End If

If you find yourself using this sort of logic in lots of places, you can
write a function to do it:

'----- start of code -----
Function ZLStoNull(sValue As String) As Variant

   If Len(sValue) > 0 Then
       ZLStoNull = sValue
   Else
       ZLStoNull = Null
   End If

End Function
'----- end of code -----

Then you could call that function wherever you make these assignments:

   Rec.FieldX = ZLStoNull(strFieldX)

You can run an update query to fix up those records that already have
zero-length strings where you'd rather have Null:

   UPDATE YourTableName
   SET FieldX = Null
   WHERE FieldX = "";

I would also recomment setting the field's AllowZeroLength property (in
table design) to No.  You would do this after having run the update query
above.  Bear in mind that doing so will cause an error to be raised any time
you try to store "" in the field, so it will cause errors in existing code
that assigns "" to the field.  That's good, if your intention is henceforth
to prevent that from happening.

An alternative to this fix is to allow zero-length strings in your code, but
change your queries to filter on Null OR ZLS.  You can do that in one handy
criterion by using the concatenation operator, like this:

   WHERE FieldX & "" <> ""

However, I don't like this approach.  I prefer that if a zero-length string
is to be treated the same as Null -- which is most of the time, in my
applications -- I just don't let the ZLS get into the field in the first
place, and use only Null to represent the no-data condition.

In addition to the excellent advice/information given, I would like to add
the following with respect to data input and the ZLS (Zero Length String)
and Null ...
------------------------------
When you enter "" (A ZLS)
- in a numeric field that IS NOT required, a Null will be stored.
- in a numeric field that IS required, you get an error.
- in a text field that allows ZLS's, a ZLS will be stored.
- in a text field that IS NOT required and DOES NOT allow ZLS's, a Null will
be stored.
- in a text field that IS required and DOES NOT allow ZLS's, you get an
error
- in an UNBOUND control, a Null is returned by the control