Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

blank values not null

Discussion in 'Information Technology' started by neeraj, Oct 28, 2005.

Tags:
  1. neeraj

    neeraj
    Expand Collapse
    Guest

    I have inherited a database with tables linked to a server. In one of the
    tables, a field say Fld1 has a few blank values. When I try to filter records
    by using filter by form using 'is not null' for Fld1, it outputs even those
    records which have a blank value for Fld1. Similarly, if I filter by form for
    'Is Null' for Fld1, it gives even those records which have a non-blank value.
    The results are the same if I build a query with criteria saying 'is null' in
    one case or 'is not null' in the other. How can I make it filter only the
    records which have a blank value in Fld1
     
  2. Loading...

    Similar Threads Forum Date
    USA Utah Sikhs bring warmth to homeless with blanket donation Breaking News Dec 21, 2013
    History The Art of Firing Blanks: Jaswant Singh and the BJP Punjab, Punjabi, Punjabiyat Aug 25, 2009
    Sikh News Security blanket over Punjab as dera deadline draws close (IANS via Yahoo! India News Breaking News May 27, 2007
    Sikh Values Blogs Oct 21, 2015
    Canada Quebec's Charter of Values: If You Have A Personal Story, Please Share It Here! Breaking News Sep 19, 2013

  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    If you don't see anything in the Field value, it can be Null or an empty
    String or even white spaces.

    If you want to trap them all, create a Calculated Field in your Query:

    Expr1: Len(Trim([Fld1] & ""))

    and in the criteria row, enter the criterion

    0

    The & "" converts a String or Null to String. Trim removes all white spaces
    so if your Field value is Null or has white spaces, the result is an empty
    String which has zero length.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "neeraj" <neeraj@discussions.microsoft.com> wrote in message
    news:3B26A60C-7417-4BA5-8403-8472AB4E7EF8@microsoft.com...
    >I have inherited a database with tables linked to a server. In one of the
    > tables, a field say Fld1 has a few blank values. When I try to filter
    > records
    > by using filter by form using 'is not null' for Fld1, it outputs even
    > those
    > records which have a blank value for Fld1. Similarly, if I filter by form
    > for
    > 'Is Null' for Fld1, it gives even those records which have a non-blank
    > value.
    > The results are the same if I build a query with criteria saying 'is null'
    > in
    > one case or 'is not null' in the other. How can I make it filter only the
    > records which have a blank value in Fld1
     
  4. neeraj

    neeraj
    Expand Collapse
    Guest

    It works but there are tens of fields in these tables. I would have to
    convert each of them to a calculated field to be able to search for blank
    values. Is there a way to convert all blanks to nulls; that would also allow
    me to Access's built in functionality 'Is Null'

    "Van T. Dinh" wrote:

    > If you don't see anything in the Field value, it can be Null or an empty
    > String or even white spaces.
    >
    > If you want to trap them all, create a Calculated Field in your Query:
    >
    > Expr1: Len(Trim([Fld1] & ""))
    >
    > and in the criteria row, enter the criterion
    >
    > 0
    >
    > The & "" converts a String or Null to String. Trim removes all white spaces
    > so if your Field value is Null or has white spaces, the result is an empty
    > String which has zero length.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "neeraj" <neeraj@discussions.microsoft.com> wrote in message
    > news:3B26A60C-7417-4BA5-8403-8472AB4E7EF8@microsoft.com...
    > >I have inherited a database with tables linked to a server. In one of the
    > > tables, a field say Fld1 has a few blank values. When I try to filter
    > > records
    > > by using filter by form using 'is not null' for Fld1, it outputs even
    > > those
    > > records which have a blank value for Fld1. Similarly, if I filter by form
    > > for
    > > 'Is Null' for Fld1, it gives even those records which have a non-blank
    > > value.
    > > The results are the same if I build a query with criteria saying 'is null'
    > > in
    > > one case or 'is not null' in the other. How can I make it filter only the
    > > records which have a blank value in Fld1

    >
    >
    >
     
  5. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    The way I posted is the most comprehensive!

    If data is imported, you can have one space, 2 or more spaces, tab
    character, etc ... as Field values and the expression I posted trap them
    all.

    Besides, converting from an empty String to Null also requires Calculated
    Fields.

    If the data is manually entered in Access, you can try the criteria:

    "" Or Is Null

    for each Field.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "neeraj" <neeraj@discussions.microsoft.com> wrote in message
    news:93EA9061-6162-4484-B29B-1274F7D8A156@microsoft.com...
    > It works but there are tens of fields in these tables. I would have to
    > convert each of them to a calculated field to be able to search for blank
    > values. Is there a way to convert all blanks to nulls; that would also
    > allow
    > me to Access's built in functionality 'Is Null'
    >
     
  6. neeraj

    neeraj
    Expand Collapse
    Guest

    The data was not manually entered in Access itself but it is coming from a
    manually filled in web survey that feeds these tables. These tables are lying
    in SQL server and I in my Access database have ODBC connectivity to them
    (pardon my technically incorrect language, if any; thats what my
    understanding is) It seems that all the blanks in at least all the fields
    that I checked could be found by searching for "". That is the number of
    results by searching for "" is the same as searching in a calculated field
    created by your formula with criteria of 0. I think blanks are all zero
    length strings. Do you agree?

    "Van T. Dinh" wrote:

    > The way I posted is the most comprehensive!
    >
    > If data is imported, you can have one space, 2 or more spaces, tab
    > character, etc ... as Field values and the expression I posted trap them
    > all.
    >
    > Besides, converting from an empty String to Null also requires Calculated
    > Fields.
    >
    > If the data is manually entered in Access, you can try the criteria:
    >
    > "" Or Is Null
    >
    > for each Field.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "neeraj" <neeraj@discussions.microsoft.com> wrote in message
    > news:93EA9061-6162-4484-B29B-1274F7D8A156@microsoft.com...
    > > It works but there are tens of fields in these tables. I would have to
    > > convert each of them to a calculated field to be able to search for blank
    > > values. Is there a way to convert all blanks to nulls; that would also
    > > allow
    > > me to Access's built in functionality 'Is Null'
    > >

    >
    >
    >
     
  7. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Only you know your data! If you are sure that all blanks from your
    DataSource are empty string "", then you can simply search for empty String
    without worrying about the white-space characters or Null.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "neeraj" <neeraj@discussions.microsoft.com> wrote in message
    news:5052553E-B11B-4B53-9FBF-34F20DB0FD86@microsoft.com...
    > The data was not manually entered in Access itself but it is coming from a
    > manually filled in web survey that feeds these tables. These tables are
    > lying
    > in SQL server and I in my Access database have ODBC connectivity to them
    > (pardon my technically incorrect language, if any; thats what my
    > understanding is) It seems that all the blanks in at least all the fields
    > that I checked could be found by searching for "". That is the number of
    > results by searching for "" is the same as searching in a calculated field
    > created by your formula with criteria of 0. I think blanks are all zero
    > length strings. Do you agree?
    >
     

Share This Page