Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013

    Unanswered: Strange Query Result


    I have created an Access database using 2010 MS Access that has a Table with data that was imported from Excel.

    I created a query to pull records where the [first_name] is blank or "is null". What I get is 10 of 13 records. For some strange reason, it doesn't pull up the other three blank records. Further, when using * under criteria, it pulls up all records, including the blank ones under [first_name].

    Any idea or thoughts why this is happening?

    Thanks in advance for your attention..


  2. #2
    Join Date
    Dec 2012
    Logan, Utah
    Couldn't even venture a guess without seeing your table definitions, the query and the data.

    If you provide the DDL and correctly formatted insert statements, it will go a long ways to helping you get an answer.

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Do the three AWOL rows have spaces, tabs, or other non-printing characters in their first_name column?

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    As Pat suggested, all 'blank' Fields aren't necessarily Null, they can have any number of non-visible characters, as mentioned, including the infamous ZLS (Zero Length String), which is why, in VBA, instead of using

    If IsNull([FieldName]) Then

    we suggest using something like

    If Nz([FieldName], "") = "" Then

    which checks for both Nulls and ZLSs.

    As I understand it, the very fact that this data starts out as Excel data greatly increases the chances of ZLSs being present.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I'd agree there are probably some non printing characters in the column
    my guess would be its probably carriage return and or line feed

    you could identify that by various queries

    select first_name, len(first_name) as NoChars from My table
    that will tell you how many characters there are in the column
    if your missing rows return 1 or two characters then you will know that there is soemthign there

    having doen that you can find precisely what characters are in there
    lets assume its the first character we are interested in

    select first_name, asc(left(first_name,1)) as firstchar from mytable
    where len(first)name between 1 and 3)
    ...and so on
    find what carcaters are returned, Im expecting to see 10 and or 13
    then write a query that gets rid of those characters, say it is 10 and or 13
    update mytable set first_name = replace (replace (first_name, chr(13),""), chr(10), "")
    where len(first_name) between 1 and 3

    you may need to tweak that query to work, but assuming it does save the query and run it whenever you import data from the same source. or of course you coudl do things the right way round, store data in your database and analyse but not store original data in Excel
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2013
    Thanks all for the prompt responses!

    The NZ query proved useful in sorting all records, including the three strange ones. The len function produced records with corresponding count of characters for all records but the three strange rows - in fact it didn't even show 0 for those three as it did others having blanks..


Posting Permissions

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