ds from access database

[user_1]

Hi,

I am developing an application with labview and access.

In the database there are duplicate records.I want to delete the duplicate records.

The scenario is:

Let there be a table named dataacquire with fields id(autogenerated),Date,datesend,value...

The duplicate records will occur in the following manner

ID                   Date                                           datesend            Value

1                     05/05/07 05:05:05 AM                 050507                30

2                      05/05/07 05:05:05 AM                                            30

I want to delete the recordwithdatasendfield empty(NULL)

the sql query is supposed to be

delete from dataacquire where Date='#05/05/07 05:05:05 AM#' and datasend=' ' .

But because of the second part of the query(datassend='') the command is not working.

How do i write the sql query to delete record whose field is NULL?

Thanks....

[RavensFan]

Try

where Date='#05/05/07 05:05:05 AM#' and datasend Is Null

'' (2 single apostrophes) is a zero length string which is different from a null value according to the labview help.

One way to format SQL queries is to open the access database and design a query that does what you want it to do, then use  menu View/SQL View to see the  QL string written out.

PS Check one more thing.  In your table up top you say "datesend", but in the query you say "datasend".

Message Edited by Ravens Fan on 05-02-2007 11:20 PM

[mikeporter]

The reason your query originally didn't work is that technically a NULL is defined as an unknown value - not an empty one. Hence you can not do comparisons directly. If a value is unknown, then all comparisons will fail:

something > NULL = false
something < NULL = false
NULL = NULL         = false
NULL != NULL       = false

You have to use the syntax 'IS NULL' or 'IS NOT NULL'. Likewise, say you have a NULL in a numeric field. Then:

NULL + 1 = NULL
NULL  - 1 = NULL
NULL * 40000000000 = NULL

Get the picture? NULL is not just empty, it's unknown.

Mike...