Re: How to test for empty Excel cell in SELECT ... WHERE


"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@xxxxxxxxx> wrote in message
news:Xns9A4F594C6D0AChrisCheneytesconet@xxxxxxxxxxxxxxxx
"Sven Pran" <no.direct@xxxxxxxxxxx> wrote in
news:47c1d003@xxxxxxxxxxxxxxxxxxxxxx:

I am doing some trials with ADO connecting to MS Excel
along the lines suggested in
http://delphi.about.com/od/database/l/aa090903a.htm

I have run into a problem when trying to SELECT only rows where
the cell in a particular column is empty (or is not empty)

The error message I receive is: "Data type mismatch in criteria
expression" and the criteria I have tried include: '' ' ', "", " "
(single or double quote pairs
with nothing or one space between them).

The cell in question contains numbers and my statements like:
SELECT ... WHERE cellname = 1611
work as expected.

How can I specify empty (or not empty) cells in my WHERE clause?

WHERE columnname IS NULL

WHERE columnname IS NOT NULL

OH DEAR!

Of course - silly me.

My only excuse is that I was working late at night.

THANKS!

regards Sven


.

Relevant Pages

  • Re: Error Checking Code, Excel 2000 & 2003
    ... Sub ErrorCheckTEST() ... Dim cell as Range, cell1 as Range ... msg= "ERROR Description is empty" ... MsgBox "ERROR Description is empty" ...
    (microsoft.public.excel.programming)
  • Re: Delete Table Rows (Blank) Macro Question
    ... I wish to just delete the empty ... Dim pCell As Word.Cell ... If pCell.RowIndex> pCurrentRow Then ... 'Current cell is not empty? ...
    (microsoft.public.word.vba.general)
  • Re: Error Checking Code, Excel 2000 & 2003
    ... 'checks each cell in row have data or no data next row if all blank end ... MsgBox "ERROR Description is empty" ... MsgBox "ERROR Type is empty" ...
    (microsoft.public.excel.programming)
  • Re: How to test for empty Excel cell in SELECT ... WHERE
    ... the cell in a particular column is empty ... The cell in question contains numbers and my statements like: ... WHERE cellname = 1611 ... WHERE columnname IS NOT NULL ...
    (borland.public.delphi.database.ado)
  • RE: Looping Through Variables
    ... i have always used Empty. ... In reference to clearing values in one instruction, its not the cell values ... Dim anyListEntry As Range ... Dim sourceWS As Worksheet ...
    (microsoft.public.excel.misc)