Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Location
    Birmingham, UK
    Posts
    19

    Question Unanswered: Evaluation of null strings in A2K3 queries

    Hi,

    I cant find any similar topics in the forums so...

    I have a database that has been converted from A2K to A2K3. One of the queries that runs in the database uses the condition 'is not null' on a text field. The query is bases on many others but ultimatly a table where the data type is defined as text.

    The problem is that although this query works fine in A2K it does not give the same results in A2K3. Instead of filtering out records with null values it includes them?!

    This has been solved by adding a query field 'len([suspectfield])' and the condition '<>"0"' but I would like to know if anyone knows the reason for this silly behaviour. Is there a coruption (or something) in the db or is 2003 just doing it wrong??

    Any ideas please

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try nz(yourfield, "0") <> "0"

    Also, IS NOT NULL is perfectly valid with A2K3, the issue most likely lies elsewhere. Did you have to do a data import or anything of that nature? Remember, a zero-length string is not the same as null.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Location
    Birmingham, UK
    Posts
    19
    Thanks, used the nz option in place of the origional condition but it still gave the same results. There was no data imports, the backend was converted from 2K to 2K3 though

    The thing that gets me is that under 2K it works fine without any extra conditions, a simple not null was enough

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Check to see what the value of the field really is. It may be a blank space etc. Whatever it is, I'm pretty sure it's not null.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It is most likely an empty string issue. Whenver I am treating the empty string and null the same way I do the following:

    FieldName & ""<>""

    It basically converts nulls to empty strings before checking to see if the result is an empty string. It's the best solution I have found to checking for null and empty string at the same time.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DCKunkle
    It is most likely an empty string issue. Whenver I am treating the empty string and null the same way I do the following:

    FieldName & ""<>""

    It basically converts nulls to empty strings before checking to see if the result is an empty string. It's the best solution I have found to checking for null and empty string at the same time.
    Hey DC

    <>""

    should be enough to remove nulls and empty strings - you shouldn't need to concatenate anything to your null.

    (NULL <> "") = NULL (i.e. not true) therefore the NULL is not returned.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    To throw my somewhat underwhelming weight behind t'others - sounds like a confusion between Nulls and Zero-Length Strings issue to me too.
    Last edited by pootle flump; 09-23-05 at 10:10.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2004
    Location
    Birmingham, UK
    Posts
    19
    There were two seperate front ends (2k and 2k3) and only one backend. The same query in the different front ends produces different results using the same data so the results should be the same wether null or zero lenght strings are involved, no?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Maara
    There were two seperate front ends (2k and 2k3) and only one backend. The same query in the different front ends produces different results using the same data so the results should be the same wether null or zero lenght strings are involved, no?
    Indeed it should be the same. What happens if you run:
    Code:
    SELECT Switch([MyCol] IS NULL, "Null_Value", [MyCol] = "", "Zero_Len_String"), *
    FROM MyTable
    WHERE Len([MyCol]) = 0
    on the offending column and table. Do both FEs return the same or different? Run it on the table itself too - not the query - to eliminate any differences between the two FE databases rather than differences between the applications.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    One thing you might check is the properties of the query. Specifically the Filter property. If you filtered the results at one time and saved it, the filter won't show in the SQL but will definitely affect the results.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •