CODEWITHSUNDEEP
sqlms-access
Mik0r
Mik0r

Reputation: 201

Only return rows that have an empty field?

I have a table where one of the fields can be empty. I'm trying to return only these rows where this field is empty. But I keep getting errors just doing WHERE field = "" ... WHERE field = '' ... WHERE field = null

Any ideas what I'm missing?

Upvotes: 3

Views: 65737

Answers (5)

Deepak N
Deepak N

Reputation: 1639

Yeah, I was facing the same But finally I tried the following mysql query it worked and saved me.

  SELECT * from your_table_name WHERE field_name IS NULL;

Go ahead with this query it will select only the rows which are empty.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

SELECT * 
  FROM MyTable 
 WHERE IIF(MyField = ' ', NULL, MyField) IS NULL;

UPDATE: here's a demonstration of how "ANSI padding" works in the Access Database Engine (ACE, Jet, whatever), which somehow seems to be necessary (surely every SQL product in the land works this way...?): just paste into any VBA (Access, Excel, Word, etc) or VB6 module and run (no references etc required): if it is true that a single space is equal to a zero-length string (ZLS) or an 'undetermined' number of spaces then you will see a list of Ys: 

Sub Fundamentals()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    With .ActiveConnection

      Dim SQL As String
      SQL = _
      "SELECT IIF(SPACE(0) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(1) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(2) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(3) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(4) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(5) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(55) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(99) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(255) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(4321) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(54321) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(654321) = SPACE(1), 'Y', 'N');"

      .Execute SQL

      Dim rs
      Set rs = .Execute(SQL)
      MsgBox rs.GetString(, , vbCr)
    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

UPDATE2:

Certainly Jet/ACE doesn't pad fields to fixed length!

Incorrect. The Access Database has a fixed width text data type generally known as NCHAR(n) (though other synonym apply) that does indeed pad column values to fixed length...

What data type is NCHAR(10) in the Access table designer?

I don't it will show in the table designer thing correctly. The Access UI still lags behind the Jet 4.0 technology, there are many such omissions. I don't have Access installed at the moment -- perhaps someone could run the following code, open the .mdb in the Access UI and tell us...?

Sub AccessNChar()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    With .ActiveConnection

      Dim Sql As String

      Sql = "CREATE TABLE TestNChar (col1 NCHAR(10));"
      .Execute Sql

      Sql = "INSERT INTO TestNChar (col1) VALUES (SPACE(1));"
      .Execute Sql

      Sql = "SELECT LEN(col1) FROM TestNChar;"

      Dim rs
      Set rs = .Execute(Sql)

      MsgBox rs.GetString
    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

Upvotes: 4

Parris Varney
Parris Varney

Reputation: 11478

Does your query have three WHERE clauses? If so, change the second two to OR

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

In SQL, a completely empty field is said to be NULL. For NULL searches you do not use equals (=), but rather the special operator IS NULL:

 SELECT * FROM table WHERE field IS NULL

Access allows you to have not NULL empty fields if you allow empty strings, this is a bad idea as it makes it difficult to distinguish visual between a NULL value and a 0-length string, so I suggest you don't permit it in your database.

Upvotes: 13

Stephen Wrighton
Stephen Wrighton

Reputation: 37819

What do you mean by errors?

But, if you want to get just those rows where the fields are empty, attempt something like thus:

SELECT * FROM MyTable WHERE LTRIM(RTRIM(ISNULL(MyField, ''))) = ''

Upvotes: 3

Related Questions