• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4154
  • 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?
0
leslieinva
Asked:
leslieinva
2 Solutions
 
YZlatCommented:
either way is fine.

you can also use:

if Len(rs("fieldname") )= 0 then
0
 
danataylorCommented:
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.
0
 
kingsfan76Commented:
1.  it's better to be a little redundant:
      if isNull(rs("FieldName") or rs("FieldName")="" then
      or:
      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

@YZlat
i think Len(rs("fieldname") ) will throw an error if the field contains a NULL value
0
 
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?
0
 
kingsfan76Commented:
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
    else
          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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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