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

Posted on 2005-04-12
Last Modified: 2008-05-16
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?
Question by:leslieinva
    LVL 35

    Expert Comment

    either way is fine.

    you can also use:

    if Len(rs("fieldname") )= 0 then
    LVL 9

    Assisted Solution

    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.
    LVL 12

    Expert Comment

    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

    Author Comment

    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?
    LVL 12

    Accepted Solution

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now