Results 1 to 7 of 7
  1. #1
    _craker_ is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    2

    Difference between (blank) and empty entries in a record?

    Can someone explain this to me?



    A customer has given me an MDB. The table entries are empty for this field, when I run this query

    SELECT * FROM Parts WHERE Part_Acceptance=''

    I get loads of results.

    In a database I have created myself using an SQL query, I get no results. I can't think of the correct SQL query to return these entries (NULL , "", ???)

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Fields can be null or ""
    variables can be null or empty.

  3. #3
    _craker_ is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    2
    Is there a way to see that in Access? If I edit the field and delete it again it retains the NULLness or empty-string ness that it started with.

    SELECT * FROM Parts WHERE Part_Acceptance=null; < returns nothing
    SELECT * FROM Parts WHERE Part_Acceptance=''; < returns nothing on one database, 100s of entries in another.

    Bottom line is, I need to refactor my SQL query to pick both these conditions up as we've got a customer who says 'Your tool is broken' because they've created a DB in a weird way I don't get!

  4. #4
    JoeM is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    3,904
    Typically, in Criteria, you would use "... Is Null" instead of "... = Null".
    See: https://www.techrepublic.com/article...%20its%20count.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe this will help: Consider null to be the absence of any data; it is an unknown. A zls (zero length string or "") is something. However it is merely a string with no characters in it. Either way, a table field looks the same with either in it. Is Null will not find records with zls in them. Nor will WHERE [someField] = "" find nulls. The reason you cannot use WHERE [someField] = Null is because NOTHING is ever equal to null - not even null itself. Thus you have to use intrinsic functions (Is Null in sql; IsNull in code).

    To find both types of records in one query you'd need WHERE [someField] = "" OR [someField] Is Null

    If you can, the better approach might be to avoid having zls values. If you can't alter the table fields to not allow zls then part of your operation probably should be to run a query against the main data that converts zls field values to null.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Just to add, numbers, including dates and Booleans can never be a zls only null if blank

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Micron View Post
    To find both types of records in one query you'd need WHERE [someField] = "" OR [someField] Is Null
    Or ... WHERE Nz([SomeField],"") = ""
    or even ... WHERE RTrim(Nz([SomeField],"")) = "" (e.g. when data are read from dbf-files originally, in which case any strings are read as fixed width ones with trailing spaces to fill read values to dbf-file field width).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 05-16-2020, 04:03 PM
  2. Difference Between Two Entries
    By MFS in forum Queries
    Replies: 3
    Last Post: 07-26-2018, 05:28 PM
  3. UPDATE blank or empty record row
    By aaendewy in forum Access
    Replies: 5
    Last Post: 06-19-2018, 08:06 PM
  4. Replies: 4
    Last Post: 11-20-2013, 03:04 PM
  5. Blank screen on empty query
    By JackieEVSC in forum Forms
    Replies: 2
    Last Post: 11-29-2011, 03:59 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums