Excel SQL Query - Where (somefield) is null - not working correctly

Question

Having a bit of trouble with an Excel SQL Query where clause.

The data in my sheet within excel (as far as I can see) is empty, there are no values and no formatting etc. in the cell. I am trying to run the following query from Outlook (in VBA).

strQuery = "Select Count(ReviewDate) as UnassignedCount FROM [PatComDB$]
Where ReviewDate is null "

But the query is returning 0.

I have searched for some solutions and have tried the below.

strQuery = "Select Count(ReviewDate) as UnassignedCount FROM [PatComDB$]
Where IIF(ReviewDate is null,0,ReviewDate) "

(Which returns 7 - the count of the field that is not null)

And have had no luck.

My connection clause is as follows (if it is any use)

Set Con = New ADODB.Connection
With Con
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=<some link>;" & _
        "Extended Properties=Excel 8.0;"
    .Open
End With

and my sheet looks something like this

IdeaID      RPA     Type        SubDate     ReviewDate

IdeaID1     RPA1234 Existing    01/01/2015  
IdeaID2     RPA1235 New         02/01/2015  
IdeaID3     RPA1236 Existing    03/01/2015  
IdeaID4     RPA0001 New         04/01/2015  01/09/2015
IdeaID5     RPA0001 New         05/01/2015  02/09/2015
IdeaID6     RPA0001 New         06/01/2015  07/09/2015
IdeaID7     RPA0001 New         07/01/2015  07/09/2015
IdeaID8     RPA0001 New         08/01/2015  07/09/2015
IdeaID9     RPA0001 New         09/01/2015  07/09/2015
IdeaID10    RPA0001 New         10/01/2015  07/09/2015

Does anyone have any ideas where I could be going wrong, be it my query or the data in my sheet perhaps?

Note: I am unfortunately restricted to Outlook and Excel in terms of software.

Thanks in advance.

EDIT:

Forgot to mention that running the following query on the same data set returns NULL

strQuery = "Select * FROM [PatComDB$] Where Index = 1"
rsARR = rs.GetRows
Debug.Print (rsARR(6, 0))

ppw · Accepted Answer · 2015-09-17 13:48:42Z

1

Figured out the answer myself after a few more attempts at code variations.

I had to change the count column to a column with data in for it to work correctly (In this case I used the Index field which will never be empty) Code used is below.

strQuery = "Select Count(Index) as UnassignedCount FROM [PatComDB$] 
Where ReviewDate = Null OR ReviewDate = 0 OR ReviewDate is null"

I used some suggestions from answers here and other answers from my research to make sure I capture every eventuality of an empty cell (I think).

answered Sep 17, 2015 at 13:48

ppw

1554 silver badges17 bronze badges

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

Comments

Clint Street · Accepted Answer · 2015-09-17 16:39:20Z

1

I am just guessing here try this:

strQuery = "Select Count(IIF(ReviewDate is null,0,ReviewDate)) as UnassignedCount FROM [PatComDB$] Where  ReviewDate = 0"

Since the above did not work try this I have used this in an access database because sometimes the data is not null it is blank instead.

 strQuery = "Select Count(ReviewDate) as UnassignedCount FROM [PatComDB$] 
 Where ReviewDate is Null Or " & Char(34) & Char(34)

Not sure if it will work for an excel query. Worth a shot

edited Sep 17, 2015 at 16:39

answered Sep 16, 2015 at 18:40

Clint Street

3391 silver badge11 bronze badges

3 Comments

ppw Over a year ago

Still no joy Clint, the query doesn't error, but I still get a '0' returned from it. Thanks anyway for your suggestion.

Clint Street Over a year ago

@ppw I have another suggestion that I have used in access with Null.

Clint Street Over a year ago

Sorry never seen your answer below before I posted.

Collectives™ on Stack Overflow

Excel SQL Query - Where (somefield) is null - not working correctly

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related