• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4151
  • Last Modified:

To Find Empty Fields is it Better to Test for Null or "" ?

3 closely related questions:

1.  If I'm using Access 2000 & ASP, is it better to test for empty text & memo fields using:

  A. the IsNull function, e.g., If IsNull(rs("fieldname")), or
  B. check for the field being equal to an empty string, e.g. , If rs("fieldname") = "" ?

2.  By default Acces sets all fields AllowZeroLength strings to False.  Would I have to change the field's AllowZeroLength property from False to True to be able to check for an empty text fields?

3.  If I want my ASP pages to be as portable as possible (easy to use if I ported the database from Access to  another database like MySQL), what would be the best solution for testing for empty text or memo fields?
2 Solutions
either way is fine.

you can also use:

if Len(rs("fieldname") )= 0 then
NULL values can be problematic.  They don't really equate to anything; zero length strings or even other null values.  In order to universally test for empty or null fields I always append a space to the tested value and then test for a single space like:

if " " & rs("fieldname") = " " then ...

That way null values or zero-length strings all test the same.
1.  it's better to be a little redundant:
      if isNull(rs("FieldName") or rs("FieldName")="" then
      if ("" & rs("FieldName")) = "" then

note that if you use  If rs("fieldname") = ""  it wouldn't work for NULL entries.
On the other hand, if you check for a fields that is not empty or null, then you can just do this:
     if rs("FieldName") <> "" then    'this will work with both null and empty

2.  if you want to allow zero-length ("") string to be inserted into text fields i believe you need to set AllowZeroLength to true.

3.  the above method should work

i think Len(rs("fieldname") ) will throw an error if the field contains a NULL value
leslieinvaAuthor Commented:
Do you folks think that YZlat's solution, testing for a zero length string:
  If Len(rs("fieldname") )= 0 then...
would be just as portable a solution as using danataylor & kingsfan76's solution of appending a space or empty string:
   If " " & rs("fieldname") = " " or If ("" & rs("FieldName")) = "" then

I would rather check for an empty length string, as I fear typos when applying the other solution, but does testing for zero length work with null fields?  Is each solution eaqually portability between databases?
From what I've tested, Len() does not work with null value.
You can tested in asp by this statement:
    if Len(NULL) = 0 then response.write("NULL is empty")

if you fear typos you can use this:

    if rs("fieldName") <> "" then
          'do nothing
          response.write("string is empty")
    end if

there are many ways to achieve the same thing, just make sure you cover both zero-length string and null value.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now