Not like; <>; != in query adodb excel

[drag1c]

Hi,

I am trying to use query like this in few different ways and it doesn't load anything.

qry = "SELECT * From db_actionlist WHERE [Status] != 'Canceled'"

- Error

qry = "SELECT * From db_actionlist WHERE [Status] <> 'Canceled'"

- Nothing

qry = "SELECT * From db_actionlist WHERE NOT ([Status] =  'Canceled'")

- Nothing

What could be issue or how I could set up statement not equal to some string?

 

[Micron]

Can you post a representative copy of your data (select/copy and paste into a post)? Or if you happen to be using lookup fields in your table, then the value Canceled is not "Canceled" - it is something else, such as a number like 2. The second example should work. The syntax of the 1st is incorrect, the other probably can't use NOT in that way, but I'm a bit fuzzy on that one (getting old I guess).

 

[drag1c]

Here are examples:

Here is example of the code

Private Sub Groupbydept() 'Group by user dept

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Action List")

'If sh.Range("A10").Value <> "" Then
'sh.Rows("10:" & Rows.Count).ClearContents
'Else
'sh.Rows("10:" & Rows.Count + 1).ClearContents
'End If

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim qry As String, i As Integer
Dim n As Long
  
qry = "SELECT * From db_actionlist WHERE [Status] <> 'Canceled'"
  
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "XXXXXXXXX" & ";" & "Jet OLEDB:Database Password= XXXXXX"

rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

sh.Range("A10").CopyFromRecordset rst
  
rst.Close
cnn.Close

End Sub

Data on sheet is empty, but it should be 2 rows filled. When I remove WHERE part, it works fine and all 4 rows are there.

 

[drag1c]

Solved...
Problem was Not Like doesn't work with 'null' fields.
I have to write something in empty rows instead null.

Please lock the topic.

 

[Micron]

Not Like Null? Null is basically "unknown" so most comparisons cannot be compared to Null. There are 2 operations for this: IsNull (vba) which is a function, and Is Null (Access sql) as in
IsNull(myTextbox) or myField Is Null

If you read this carefully I expect you'll get a lot out of it.

Microsoft Access tips: Optimizing queries

How to get JET (the database engine in Microsoft Access) to execute queries quickly

allenbrowne.com

BTW, posting pics of data is not the same as posting the data. I for one won't try to manually transpose a lot of data from a screen picture to a sheet in an effort to solve an issue, but that's just me.

 

[smozgur]

Problem was Not Like doesn't work with 'null' fields.
I have to write something in empty rows instead null.

If the data structure cannot be changed, then there will be always null values. You can use the following instead of editing records.

qry = "SELECT * From db_actionlist WHERE [Status] <> 'Canceled' OR [Status] IS null"