Query with empty cells only works with "LEN(name) > 1 or LEN(name) < 2"

Question

In my program (vb.net) I can only start a oleDBQuery to access a Acces 2000 Table when there are some cells empty, when I use "LEN(EnterName) > 1 or LEN(EnterName) < 2", which is strange, cause > 1 and <2 would just select any cell just like without LEN.

If i dont use LEN then I get this Error Message

"The given transformation is invalid"

This isn't the only problem. When I use LEN it does not return the empty Cells, which i need.

Would be cool if you could help me.

Picture of Database and vb Application (using LEN) sry I needed to pixelate anything but this one line, cause of data protection. If you need more information, please ask me!

Code:

    Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\WE2015_NEU dura logo.mdb;Persist Security Info=False;")

        'fills the combobox with information from the database
        '═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
        connection.Open()                                                                                                                      '║
        Dim command_Name_Firmen As New OleDbCommand("SELECT Betrieb FROM MaterialHist Where LEN(Betrieb) > 1 or LEN(Betrieb) < 2", connection) '║
        Dim reader_Name_Firmen As OleDbDataReader = command_Name_Firmen.ExecuteReader()                                                        '║
        While reader_Name_Firmen.Read()                                                                                                        '║
            DatenbankboxDrölf.Items.Add(reader_Name_Firmen.GetString(0))   ''Here in the Brackets is the error if i would not use LEN          '║(Datenbankdrölf = Combobox)
        End While                                                                                                                              '║
        reader_Name_Firmen.Close()                                                                                                             '║
        connection.Close()                                                                                                                     '║
        '═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

    End Using

Can you give us some context please: what are these "cells"? Are you referring to Excel data? — Andrew Morton, Mar 02 '16 at 13:39
Sry Guys writed it in a rush, cause working time was over, and then I forgot this Question ._. — Neo127, Mar 16 '16 at 07:35
Your question is really unclear. What do you want from your query? Rows where `Betrieb` is empty? Or not empty? Which query returned your error message? — Andre, Mar 16 '16 at 09:39
Watch the Picture! Then you see what i Mean. Some Cells are Empty and some Not. If I Use LEN Then it Returns only the filled cells, but I need the empty cells to! — Neo127, Mar 16 '16 at 10:05
If you want all rows (please stop using the word "cells"), why do you have a WHERE clause at all? Again: What exactly do you want your query to return? — Andre, Mar 16 '16 at 11:14
@Andre cause without it spits out the error Message "The given transformation is invalid" — Neo127, Mar 17 '16 at 07:06

score 0 · Accepted Answer · edited May 23 '17 at 11:45

0

It seems you need a proper handling of NULL values, using IsDbNull()

See vb.net - read null value from database

edited May 23 '17 at 11:45

Community

1
1

answered Mar 17 '16 at 07:50

Andre

24,160
6
28
67

You dont know how much I love you for that <3 <3 <3 <3 THX – Neo127 Mar 17 '16 at 08:01

Query with empty cells only works with "LEN(name) > 1 or LEN(name) < 2"

1 Answers1