SQL Null statement not working MS Access

Question

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)

Check whether the field contains an empty string: WHERE Len(workername) = 0 — HansUp
– HansUp, Commented Jun 3, 2015 at 1:37

Sachu · Accepted Answer · 2015-06-03 03:04:37Z

2

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, ''))) = ''"

answered Jun 3, 2015 at 3:04

Sachu

7,7549 gold badges59 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lilly Over a year ago

EVERYONE should literally up vote this!!! I have looked EVERYWHERE for this issue and this was the only one that worked!

Prime03 · Accepted Answer · 2015-06-03 00:34:50Z

0

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

answered Jun 3, 2015 at 0:34

Prime03

3503 silver badges9 bronze badges

2 Comments

Lilly Over a year ago

For some odd reason an Expr1:IsNull([workername]) but workername is literally in the drop down as an option and the code worked for the 900+ other records so it's very odd.

Prime03 Over a year ago

Don't you just love Access :)

Collectives™ on Stack Overflow

SQL Null statement not working MS Access

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related