CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Lilly
Lilly

Reputation: 233

SQL Null statement not working MS Access

So I'm not really sure what is going on here but in my database I have a table that has 1000 records and 36 of them have [workername] empty. I was trying to run this SQL to select the unassigned, empty [workername] records to assign but nothing populates when I put the code in query design and view mode. I genuinely have no idea why this is not working.

strSQL = "SELECT IntakeID, 
                 caseid, 
                 [Program], 
                 [language] 
          FROM Intake 
          WHERE workername Is Null"
     Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Upvotes: 1

Views: 895

Answers (2)

Sachu
Sachu

Reputation: 7766

Try this code..Here first it will replace null value to '' using Nz and if not null it will trim the value to make sure there is no space and check is it equal to '' means empty..hope it will help

"SELECT IntakeID, 
caseid, 
[Program], 
[language] 
FROM
Intake WHERE  LTRIM(RTRIM(Nz(workername, ''))) = ''"

Upvotes: 2

Prime03
Prime03

Reputation: 350

That query looks valid, so I would question whether the column is actually null.

You could try the following to find out for sure

SELECT 
    IntakeID, 
    caseid, 
    [Program], 
    [language] ,
    ISNULL(workername)
FROM Intake 
  ORDER BY ISNULL(workername) ASC

Upvotes: 0

Related Questions